Copy Table Data from Diffenent Server DB to Diffrent Server DB -
i have table structure in 1 server db , want copy data server db table. how achieve ?
note: (failed cases)
attempt 1: tried backup , restore, failed due versioning issue (10.50.2500 not matching 10.00.4064).
attempt 2: export , import, validation error , etc...no copy source destination
attempt 3: (failed due different servers)
insert [db_name]..[dbo].[table_name] select * [db_name]..[dbo].[table_name] (instead of above syntax used have parsing error)
insert [server_name].[db_name]..[dbo].[table_name] select * [server_name].[db_name]..[dbo].[table_name]
there many ways -
1. generate cvs script , export data (it's working table structure):
declare @tablename sysname , @objectid int declare [tables] cursor read_only fast_forward local select '[' + s.name + '].[' + t.name + ']' , t.[object_id] ( select distinct t.[schema_id] , t.[object_id] , t.name sys.objects t (nowait) join sys.partitions p (nowait) on p.[object_id] = t.[object_id] p.[rows] > 0 , t.[type] = 'u' ) t join sys.schemas s (nowait) on t.[schema_id] = s.[schema_id] t.name in ('<your_table_name>') open [tables] fetch next [tables] @tablename , @objectid declare @sqlinsert nvarchar(max) , @sqlcolumns nvarchar(max) , @sqltinycolumns nvarchar(max) while @@fetch_status = 0 begin select @sqlinsert = '' , @sqlcolumns = '' , @sqltinycolumns = '' ;with cols ( select c.name , datetype = t.name , c.column_id sys.columns c (nowait) join sys.types t (nowait) on c.system_type_id = t.system_type_id , c.user_type_id = t.user_type_id c.[object_id] = @objectid , c.is_computed = 0 , t.name not in ('xml', 'geography', 'geometry', 'hierarchyid') ) select @sqltinycolumns = stuff(( select ', [' + c.name + ']' cols c order c.column_id xml path, type, root).value('.', 'nvarchar(max)'), 1, 2, '') , @sqlcolumns = stuff((select char(13) + case when c.datetype = 'uniqueidentifier' ' + '';'' + isnull('''' + cast([' + c.name + '] varchar(max)) + '''', ''null'')' when c.datetype in ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary') ' + '';'' + isnull('''' + cast(replace([' + c.name + '], '''', '''''''') nvarchar(max)) + '''', ''null'')' when c.datetype = 'datetime' ' + '';'' + isnull('''' + convert(varchar, [' + c.name + '], 120) + '''', ''null'')' else ' + '';'' + isnull(cast([' + c.name + '] nvarchar(max)), ''null'')' end cols c order c.column_id xml path, type, root).value('.', 'nvarchar(max)'), 1, 10, 'char(13) + '''' +') declare @sql nvarchar(max) = ' set nocount on; declare @sql nvarchar(max) = '''' , @x int = 1 , @count int = (select count(1) ' + @tablename + ') if exists( select 1 tempdb.dbo.sysobjects id = object_id(''tempdb..#import'') ) drop table #import; select ' + @sqltinycolumns + ', ''rownumber'' = row_number() on (order ' + @sqltinycolumns + ') #import ' + @tablename + ' while @x < @count begin select @sql = stuff(( select ' + @sqlcolumns + ' + ''''' + ' #import rownumber between @x , @x + 9 xml path, type, root).value(''.'', ''nvarchar(max)''), 1, 1, '''') print(@sql) select @x = @x + 10 end' exec sys.sp_executesql @sql fetch next [tables] @tablename , @objectid end close [tables] deallocate [tables] output:
1;em;0;null;ken;j;sánchez;null;0;92c4279f-1207-48a3-8448-4636514eb7e2;2003-02-08 00:00:00 2;em;0;null;terri;lee;duffy;null;1;d8763459-8aa8-47cc-aff7-c9079af79033;2002-02-24 00:00:00 3;em;0;null;roberto;null;tamburello;null;0;e1a2555e-0828-434b-a33b-6f38136a37de;2001-12-05 00:00:00 4;em;0;null;rob;null;walters;null;0;f2d7ce06-38b3-4357-805b-f4b6b71c01ff;2001-12-29 00:00:00 2. generate insert statements script , insert data (it's working table structure):
declare @tablename sysname , @objectid int , @isimportidentity bit = 1 declare [tables] cursor read_only fast_forward local select '[' + s.name + '].[' + t.name + ']' , t.[object_id] ( select distinct t.[schema_id] , t.[object_id] , t.name sys.objects t (nowait) join sys.partitions p (nowait) on p.[object_id] = t.[object_id] p.[rows] > 0 , t.[type] = 'u' ) t join sys.schemas s (nowait) on t.[schema_id] = s.[schema_id] t.name in ('<your_table_name>') open [tables] fetch next [tables] @tablename , @objectid declare @sqlinsert nvarchar(max) , @sqlcolumns nvarchar(max) , @sqltinycolumns nvarchar(max) while @@fetch_status = 0 begin select @sqlinsert = '' , @sqlcolumns = '' , @sqltinycolumns = '' ;with cols ( select c.name , datetype = t.name , c.column_id sys.columns c (nowait) join sys.types t (nowait) on c.system_type_id = t.system_type_id , c.user_type_id = t.user_type_id c.[object_id] = @objectid , (c.is_identity = 0 or @isimportidentity = 1) , c.is_computed = 0 , t.name not in ('xml', 'geography', 'geometry', 'hierarchyid') ) select @sqlinsert = 'insert ' + @tablename + ' (' + stuff(( select ', [' + c.name + ']' cols c order c.column_id xml path, type, root).value('.', 'nvarchar(max)'), 1, 2, '') + ')' , @sqltinycolumns = stuff(( select ', ' + c.name cols c order c.column_id xml path, type, root).value('.', 'nvarchar(max)'), 1, 2, '') , @sqlcolumns = stuff((select char(13) + case when c.datetype = 'uniqueidentifier' ' + '', '' + isnull('''''''' + cast([' + c.name + '] varchar(max)) + '''''''', ''null'')' when c.datetype in ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary') ' + '', '' + isnull('''''''' + cast(replace([' + c.name + '], '''''''', '''''''''''' ) nvarchar(max)) + '''''''', ''null'')' when c.datetype = 'datetime' ' + '', '' + isnull('''''''' + convert(varchar, [' + c.name + '], 120) + '''''''', ''null'')' else ' + '', '' + isnull(cast([' + c.name + '] nvarchar(max)), ''null'')' end cols c order c.column_id xml path, type, root).value('.', 'nvarchar(max)'), 1, 10, 'char(13) + '', ('' +') declare @sql nvarchar(max) = ' set nocount on; declare @sql nvarchar(max) = '''' , @x int = 1 , @count int = (select count(1) ' + @tablename + ') if exists( select 1 tempdb.dbo.sysobjects id = object_id(''tempdb..#import'') ) drop table #import; select ' + @sqltinycolumns + ', ''rownumber'' = row_number() on (order ' + @sqltinycolumns + ') #import ' + @tablename + ' while @x < @count begin select @sql = ''values '' + stuff(( select ' + @sqlcolumns + ' + '')''' + ' #import rownumber between @x , @x + 9 xml path, type, root).value(''.'', ''nvarchar(max)''), 1, 2, char(13) + '' '') + '';'' print(''' + @sqlinsert + ''') print(@sql) select @x = @x + 10 end' exec sys.sp_executesql @sql fetch next [tables] @tablename , @objectid end close [tables] deallocate [tables] output:
insert [person].[person] ([businessentityid], [persontype], [namestyle], [title], [firstname], [middlename], [lastname], [suffix], [emailpromotion], [rowguid], [modifieddate]) values (1, 'em', 0, null, 'ken', 'j', 'sánchez', null, 0, '92c4279f-1207-48a3-8448-4636514eb7e2', '2003-02-08 00:00:00') , (2, 'em', 0, null, 'terri', 'lee', 'duffy', null, 1, 'd8763459-8aa8-47cc-aff7-c9079af79033', '2002-02-24 00:00:00') , (3, 'em', 0, null, 'roberto', null, 'tamburello', null, 0, 'e1a2555e-0828-434b-a33b-6f38136a37de', '2001-12-05 00:00:00') , (4, 'em', 0, null, 'rob', null, 'walters', null, 0, 'f2d7ce06-38b3-4357-805b-f4b6b71c01ff', '2001-12-29 00:00:00') , (5, 'em', 0, 'ms.', 'gail', 'a', 'erickson', null, 0, 'f3a3f6b4-ae3b-430c-a754-9f2231ba6fef', '2002-01-30 00:00:00') , (6, 'em', 0, 'mr.', 'jossef', 'h', 'goldberg', null, 0, '0dea28fd-effe-482a-afd3-b7e8f199d56f', '2002-02-17 00:00:00') , (7, 'em', 0, null, 'dylan', 'a', 'miller', null, 2, 'c45e8ab8-01be-4b76-b215-820c8368181a', '2003-03-05 00:00:00') , (8, 'em', 0, null, 'diane', 'l', 'margheim', null, 0, 'a948e590-4a56-45a9-bc9a-160a1cc9d990', '2003-01-23 00:00:00') , (9, 'em', 0, null, 'gigi', 'n', 'matthew', null, 0, '5fc28c0e-6d36-4252-9846-05caa0b1f6c5', '2003-02-10 00:00:00') , (10, 'em', 0, null, 'michael', null, 'raheem', null, 2, 'ca2c740e-75b2-420c-9d4b-e3cbc6609604', '2003-05-28 00:00:00'); 3. use data comparator like: dbforge data compare sql server
4. use linked servers: msdn
Comments
Post a Comment