question

Bugmesh avatar image
Bugmesh asked

Moving data from all tables (in a database) on one server to another database on a different server.

I am working on a script to update "just the data" from tables on one server to another. I have Linked Server connections on both servers so I was wondering if I am on the right track here and if I have my logic correct. I appreciate any assistance USE AA_DATA GO EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' GO EXEC sp_msforeachtable 'DELETE FROM PRODMCNTSQL002..?' GO EXEC sp_MSforeachtable @command1 = 'INSERT INTO PRODMCNTSQL002..? SELECT * FROM PRODMCNTSQL001..?' GO EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all' GO
tsqldatamove
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How many tables? How much data? Why cannot you do BACKUP --> RESTORE?
0 Likes 0 ·
Because I do not want to alter the objects in the database. I just need the data.
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
Tim avatar image
Tim answered
This is the code I use that will generate the insert statements and takes into consideration identity insert. You can still use your constraint scripts. SET NOCOUNT ON declare @name sysname declare @sql varchar(max) Declare @myTable sysname declare @columnnames as varchar(max) declare db_cursor cursor for select name from sys.tables order by 1 open db_cursor fetch next from db_cursor into @name while @@FETCH_STATUS = 0 begin SET @myTable = @name SET @columnnames = '[' SELECT @columnnames=@columnnames+sc.Column_Name+'],[' FROM information_schema.columns sc WHERE Table_Name = @myTable ORDER BY Ordinal_Position; SET @columnnames=left(@columnnames,len(@columnnames)-2) IF @name IN (select TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1) BEGIN SET @sql = 'SET IDENTITY_INSERT SecondaryDB.dbo.[' + @name +'] ON INSERT into SecondaryDB.dbo.[' + @name + '] ( ' + @columnnames + ' ) select * from [PrimaryDB].[dbo].[' + @name + ']; SET IDENTITY_INSERT SecondaryDB.dbo.[' + @name +'] OFF print ''['+ @name +'] imported'''; END ELSE BEGIN SET @sql = 'INSERT into SecondaryDB.dbo.[' + @name + '] ( ' + @columnnames + ' ) select * from [PrimaryDB].[dbo].[' + @name + '] print ''['+ @name +'] imported'''; END print @sql fetch next from db_cursor into @name end close db_cursor deallocate db_cursor
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Tim, Let me take a look at this. I appreciate your assistance
1 Like 1 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
How about these options: 1. Copy database wizard 2. right click on your database, select tasks->generate scripts. Choose objects. In the 'Scripting options' click advanced, at 'types of data to script' select 'Schema and Data' (or whatever you want) and make a decision at 'Table/view options'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.