I want to copy all the data from one database to another. The newer database
gets built with a script, and has all empty tables. The older database has
all the data in it. The newer database will never remove tables or fields,
and will only have new tables and/or fields. I want to turn off all
constraints, and copy the data from the older database into the newer
database. Is there a way to read the system tables to get a list of all the
"user defined" tables, and dynamically build sql statements along the lines
of "Insert Into NewDatabase.MyTable (Select * From OldDatabase)"

I know my insert statements will work - if I can dynamically create this,
and loop through the tables, then I would not have to even name the tables
individually. I am also not sure about looping code to do this in
TransactSQL. Help would be appreciated.

Derek Hart

Something like this may work.

declare @sql varchar(8000)
declare @table_name varchar(256)

SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'

DECLARE table_list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
OPEN table_list

FETCH NEXT FROM table_list INTO
@table_name

WHILE @@FETCH_STATUS = 0
BEGIN


SET @sql = 'Insert into NewDB..' + @table_name + ' ' + '(Select * From
OldDatabase..' + @table_name + ' ) '

--print @sql
--EXEC (@SQL)


FETCH NEXT FROM table_list INTO
@table_name

END

DEALLOCATE table_list

Great code. Thank you. I posted another message:

I want to copy a table of data from a source SQL Server to a destination SQL
Server. The destination will be empty. The destination may not have some
fields that the source has, but the source will have all the fields the
destination has. DTS seems to want to name every field, but I want to do an
insert without having to name the fields every time. Is there a way to
insert all the fields "Insert Into NewDatabase.myTable Select * From
OldDatabase?

So your code is great to work dynamically to do this in a stored procedure,
but I don't know how to execute this with DTS while pointing to two
different servers? Should I even use DTS to do this? Maybe I should
dynamically create a script to talk to the two servers. I got errors when I
wrote code like this, however:

Insert Into Server1.myTable.myField (Select * From Server2.myTable.myField)

Please let me know how to pull this one off.

Derek Hart


"Marty" <frank0288 (AT) yahoo (DOT) com> wrote