Inserting Data using INSERT INTO

[MrMystery]

Help!
I have a large number of tables where I want to copy the data from one database to another of the same structure, overwriting the existing data.
I don't want to use the DTS wizard, as this will mean selecting each table in the list and changing the transform properties to delete data rather than append.
Can someone tell me what the T-SQL syntax would be to do the same thing, as I can write a SQL script more easily.

 

[mwolf00]

INSERT INTO myDb.dbo.myTable SELECT * FROM otherDb.dbo.otherTable


Table structure must be identical.... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[MrMystery]

Will this overwrite existing data? That is the important thing for me.

 

[rt63]

A smaple code


declare @sql varchar(200)

set @sql = 'truncate destination_db..tablename'
execute(@sql)

set @sql = 'insert into destination_db..tablename select * from source_db..tablename'
execute(@sql)


You will need to figure out a method by which you can write this code in a loop for each table you want to copy. Some possible ways could be to use sysobjects (where type = 'U') or create a control table which contains a list of tables to be copied. You may also need to remove FK constraints and reapply after copying or sequence the data copy so that there are no Foreign Key issues.



RT

 

[Sammy145]

DTS is easier but

this method TEST IT FIRST =)

Use Northwind
SELECT *
INTO PUBS.dbo.Picture2
FROM Picture

what this does is selects Picture from northwind and creates table in PUBs called Picture2 and inserts the data

You will have to drop the table in PUBS every time you run this scrript

 

[mwolf00]

My method will APPEND the data - not overwrite... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com