Re: Dynamically Copy Data

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@xxxxxxxxx> wrote in message
news:1146769832.401084.318600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages
Loading