copying all the records from one table to another

[bobby123]

Hi Forum

Is it possible to copy all the records from one table to another in one action, I can copy single records one at a time no problem.

But was looking for a way to copy all the records at once then delete the all the records from the first table so it can be populated again with new records.

Thanks

Robert

[kingcarol]

Re: copying all the records from one table to another

Don't know if yours is an sql database of some kind. If so, seems like executing something similar to the following 2 sql statements in Xbasic callback might work:

To insert all records from TableA into TableB:
INSERT INTO TableB SELECT TableA.* FROM TableA

To delete all records from TableA:
DELETE TableA.* FROM TableA

If yours is an sql type database, the Alpha article at: http://www.downloads.alphasoftware.c...SQLTables.html could help.
Try finding 'Executing Insert, Updates and Deletes' on that page.

[bobby123]

Re: copying all the records from one table to another

Hi Carol

Yes I am using SQL connection, I will try it out when i get back to the office

Thanks Again

Robert

[kingcarol]

Re: copying all the records from one table to another

There's some good info in that article about making sure your insert query executed properly. In your case, I would think you would want to check that your records did get inserted into the other table before deleting them.

[MarionT]

Re: copying all the records from one table to another

to add a bit to what Carol has said -

INSERT INTO TableB SELECT TableA.* FROM TableA
works if the columns in TableA and TableB are identical and in identical sequence

INSERT INTO TableB (Col1, Col3, Col 2, ...) Select Col1, Col2, Col3, ... from TableB
works if the columns in TableA and TableB are not identical or in identical sequence

[kingcarol]

Re: copying all the records from one table to another

I wondered whether to say that, too, Marion. There's always the 'works if', right?

[JimDK]

Re: copying all the records from one table to another

I am using this bit of code when I am uploading data from Excel into a table.

dim cn as sql::Connection
dim sql as c
dim vinfo as c

IF cn.open("::name::trade")
sql = "DROP TABLE technosis_old;"
vinfo = cn.execute(sql)
sql = "CREATE TABLE technosis_old LIKE technosis;"
vinfo = cn.execute(sql)
sql = "INSERT technosis_old SELECT * FROM technosis;"
vinfo = cn.execute(sql)
sql = "truncate technosis;"
vinfo = cn.execute(sql)
ELSE
PrepareTechnosisLoad = "alert('Unable to connect to the Database';)"
END IF

the code is:
1. deleting the previous 'old' copy of the table
2. creating a new 'old' table based on the original table
3. filling the new table with the values from the original
4. deleting the content of the original one

and after that filling the original one with the data from excel.

[bobby123]

Re: copying all the records from one table to another

Hi Jim

Thanks, I will give this a go along with the other suggestions

Thanks Again

Robert