• Welcome, Guest. Please login.
 
August 19, 2019, 08:10:23 am

News:

Welcome to the SQLitening support forums!


Copy table into new table

Started by dmtulsa, May 19, 2011, 07:06:18 pm

Previous topic - Next topic

dmtulsa

May 19, 2011, 07:06:18 pm Last Edit: May 19, 2011, 07:09:49 pm by dmtulsa
What is the best / correct way to copy an entire table into a new one? Or can I just add a table with the data to a db3 file.

Here's the problem, when I started this project my database had just one table (parts). Since then someone has been adding data to it in a different location. I the mean time I've added another table to the database (pictures). So of course the to Database have different data as the ( parts) table, the only table in the .db3 they have, has been updated daily while I've been working on bugs and updating the (pictures table). Hence the .db3 I have has both tables. I hope this makes a little bit of sense.

So I need to ether replace my (PARTS) table with the other one or copy the data. I can copy to a new table then rename it back to PARTS as per the following code but is there a better way

Sub CopyDB()
Local vstr  As String
Local SqlExpr As String
Local Rec As String
Local lResult As Long

xPath$ =  "usiParts.db3"     '"sample.db3" '
slOpen xPath$, "C"

'make new table
slExe "Create table if not exists newparts (" & $fieldStr & ")","E1"
'read for old table 
lResult = slSel("SELECT *, rowid FROM parts ",%rSetParts,"E")
GetsqlError lResult

'copy data row by row
Do While slGetRow(%rSetParts)
   'Incr recn& 'record number
   rStr$ = slFN("RowID",%rSetParts)
      rec = slFN("discription",%rSetParts) & $Nul & slFN("pn",%rSetParts) & $Nul &  slFN("package",%rSetParts)_
       & $Nul &  slFN("qty",%rSetParts) & $Nul &  slFN("suppler",%rSetParts) & $Nul &  slFN("volt",%rSetParts)_
       & $Nul &  slFN("optvolts",%rSetParts) & $Nul &  slFN("ohms",%rSetParts) & $Nul &  slFN("optohms",%rSetParts)_
       & $Nul &  slFN("amps",%rSetParts) & $Nul &  slFN("optamps",%rSetParts) & $Nul &  slFN("cap",%rSetParts)_
       & $Nul &  slFN("optcap",%rSetParts) &$Nul &  slFN("power",%rSetParts) & $Nul &  slFN("optwatts",%rSetParts)_
       & $Nul &  slFN("inductance",%rSetParts) & $Nul &  slFN("optind",%rSetParts) & $Nul &  slFN("tol",%rSetParts)_
       & $Nul &  slFN("memo",%rSetParts)
       'add to new table
       slExe slBuildInsertOrUpdate ("NewParts", rec, $fieldStr)
       'use SQLiteExpert to rename NewParts to Parts
Loop
slCloseSet(%rSetParts)
End Sub


I'm sorry if this is common stuff but this is my first database project. I've learned a lot so far. Guess I was looking for a command / function like copy table.

Thanks
Doug

Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

dmtulsa

Fredrick,

Well that certainly is a lot easier. I thought there would be something like that.

Thank you

cj

May 30, 2017, 12:31:26 pm #3 Last Edit: May 30, 2017, 01:12:29 pm by cj
-- test table to be backed-up;
begin;
drop table if exists test;
create table test(c1,c2,c3);
insert into test values('a','b','c');
end;

-- create backup
begin;
drop table if exists testbackup;
create table testbackup(c1,c2,c3);
insert into testbackup select * from test;
end;

--  backup without needing to know structure of test
begin;
drop table if exists testbackup2;
create table testbackup2 as select * from test;
end;


-- more to come at a later time;