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
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
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'