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
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.
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'
18 People are following this question.
