J'ai la structure de table dans une DB de server et je veux copyr des données à une autre table de DB de server. Comment l'atteindre?
Remarque: (cas ayant échoué)
Tentative 1: j'ai essayé de sauvegarder et de restaurer, il a échoué en raison d'un problème de version (10.50.2500 ne correspond pas à 10.00.4064).
Tentative 2: Exportation et import, avec erreur de validation et etc … pas de copy de la source à la destination
Tentative 3: (a échoué en raison de différents servers)
INSERT INTO [DB_NAME]..[dbo].[TABLE_NAME] SELECT * FROM [DB_NAME]..[dbo].[TABLE_NAME]
(au lieu de la syntaxe ci-dessus j'ai utilisé comme ça aussi avoir une erreur d'parsing)
INSERT INTO [SERVER_NAME].[DB_NAME]..[dbo].[TABLE_NAME] SELECT * FROM [SERVER_NAME].[DB_NAME]..[dbo].[TABLE_NAME]
Il y a plusieurs façons de le faire –
1. Générer CVS par ce script et exporter des données (cela fonctionne pour n'importe quelle structure de table):
DECLARE @TableName SYSNAME , @ObjectID INT DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR SELECT '[' + s.name + '].[' + t.name + ']' , t.[object_id] FROM ( SELECT DISTINCT t.[schema_id] , t.[object_id] , t.name FROM sys.objects t WITH (NOWAIT) JOIN sys.partitions p WITH (NOWAIT) ON p.[object_id] = t.[object_id] WHERE p.[rows] > 0 AND t.[type] = 'U' ) t JOIN sys.schemas s WITH (NOWAIT) ON t.[schema_id] = s.[schema_id] WHERE t.name IN ('<your_table_name>') OPEN [tables] FETCH NEXT FROM [tables] INTO @TableName , @ObjectID DECLARE @SQLInsert NVARCHAR(MAX) , @SQLColumns NVARCHAR(MAX) , @SQLTinyColumns NVARCHAR(MAX) WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLInsert = '' , @SQLColumns = '' , @SQLTinyColumns = '' ;WITH cols AS ( SELECT c.name , datetype = t.name , c.column_id FROM sys.columns c WITH (NOWAIT) JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id WHERE c.[object_id] = @ObjectID AND c.is_computed = 0 AND t.name NOT IN ('xml', 'geography', 'geometry', 'hierarchyid') ) SELECT @SQLTinyColumns = STUFF(( SELECT ', [' + c.name + ']' FROM cols c ORDER BY c.column_id FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '') , @SQLColumns = STUFF((SELECT CHAR(13) + CASE WHEN c.datetype = 'uniqueidentifier' THEN ' + '';'' + ISNULL('''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''', ''NULL'')' WHEN c.datetype IN ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary') THEN ' + '';'' + ISNULL('''' + CAST(REPLACE([' + c.name + '], '''', '''''''') AS NVARCHAR(MAX)) + '''', ''NULL'')' WHEN c.datetype = 'datetime' THEN ' + '';'' + ISNULL('''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''', ''NULL'')' ELSE ' + '';'' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), ''NULL'')' END FROM cols c ORDER BY c.column_id FOR 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) FROM ' + @TableName + ') IF EXISTS( SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#import'') ) DROP TABLE #import; SELECT ' + @SQLTinyColumns + ', ''RowNumber'' = ROW_NUMBER() OVER (ORDER BY ' + @SQLTinyColumns + ') INTO #import FROM ' + @TableName + ' WHILE @x < @count BEGIN SELECT @SQL = STUFF(( SELECT ' + @SQLColumns + ' + ''''' + ' FROM #import WHERE RowNumber BETWEEN @x AND @x + 9 FOR XML PATH, TYPE, ROOT).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''') PRINT(@SQL) SELECT @x = @x + 10 END' EXEC sys.sp_executesql @SQL FETCH NEXT FROM [tables] INTO @TableName , @ObjectID END CLOSE [tables] DEALLOCATE [tables]
Sortie:
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. Générez des instructions INSERT par ce script et insérez des données (cela fonctionne pour n'importe quelle structure de table):
DECLARE @TableName SYSNAME , @ObjectID INT , @IsImportIdentity BIT = 1 DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR SELECT '[' + s.name + '].[' + t.name + ']' , t.[object_id] FROM ( SELECT DISTINCT t.[schema_id] , t.[object_id] , t.name FROM sys.objects t WITH (NOWAIT) JOIN sys.partitions p WITH (NOWAIT) ON p.[object_id] = t.[object_id] WHERE p.[rows] > 0 AND t.[type] = 'U' ) t JOIN sys.schemas s WITH (NOWAIT) ON t.[schema_id] = s.[schema_id] WHERE t.name IN ('<your_table_name>') OPEN [tables] FETCH NEXT FROM [tables] INTO @TableName , @ObjectID DECLARE @SQLInsert NVARCHAR(MAX) , @SQLColumns NVARCHAR(MAX) , @SQLTinyColumns NVARCHAR(MAX) WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLInsert = '' , @SQLColumns = '' , @SQLTinyColumns = '' ;WITH cols AS ( SELECT c.name , datetype = t.name , c.column_id FROM sys.columns c WITH (NOWAIT) JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id WHERE c.[object_id] = @ObjectID AND (c.is_identity = 0 OR @IsImportIdentity = 1) AND c.is_computed = 0 AND t.name NOT IN ('xml', 'geography', 'geometry', 'hierarchyid') ) SELECT @SQLInsert = 'INSERT INTO ' + @TableName + ' (' + STUFF(( SELECT ', [' + c.name + ']' FROM cols c ORDER BY c.column_id FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' , @SQLTinyColumns = STUFF(( SELECT ', ' + c.name FROM cols c ORDER BY c.column_id FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '') , @SQLColumns = STUFF((SELECT CHAR(13) + CASE WHEN c.datetype = 'uniqueidentifier' THEN ' + '', '' + ISNULL('''''''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''''''', ''NULL'')' WHEN c.datetype IN ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary') THEN ' + '', '' + ISNULL('''''''' + CAST(REPLACE([' + c.name + '], '''''''', '''''''''''' ) AS NVARCHAR(MAX)) + '''''''', ''NULL'')' WHEN c.datetype = 'datetime' THEN ' + '', '' + ISNULL('''''''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''''''', ''NULL'')' ELSE ' + '', '' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), ''NULL'')' END FROM cols c ORDER BY c.column_id FOR 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) FROM ' + @TableName + ') IF EXISTS( SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(''tempdb..#import'') ) DROP TABLE #import; SELECT ' + @SQLTinyColumns + ', ''RowNumber'' = ROW_NUMBER() OVER (ORDER BY ' + @SQLTinyColumns + ') INTO #import FROM ' + @TableName + ' WHILE @x < @count BEGIN SELECT @SQL = ''VALUES '' + STUFF(( SELECT ' + @SQLColumns + ' + '')''' + ' FROM #import WHERE RowNumber BETWEEN @x AND @x + 9 FOR 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 FROM [tables] INTO @TableName , @ObjectID END CLOSE [tables] DEALLOCATE [tables]
Sortie:
INSERT INTO [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. Utilisez n'importe quel comparateur de données comme: Comparaison des données dbForge pour SQL Server
4. Utilisez les servers liés: MSDN
Vous pouvez définir un DB source en tant que server lié sur une database de destination
Ensuite, exécutez simplement sur la database de destination:
SELECT * INTO [TABLE_NAME] FROM [SOURCEDB]..[dbo].[TABLE_NAME]
Cette command crée une structure de table et insère toutes les lignes de la source à la destination.
Si vous devez transférer des index, des contraintes, …. vous pouvez utiliser une command d'interface "Generate Script" Enterprise Manager dans le server MS SQL.
Aussi sur SQL 2008 vous pouvez utiliser la command generate script interface avec datatables incluses. Il vous suffit donc de générer un script sur la database source pour vos tables et d'exécuter ce script sur la database de destination. Voici quelques liens:
SQL SERVER – 2008 – Copier la database avec des données – Générer T-SQL pour insert des données d'une table à une autre
SO: dans datatables de script SQL Server 2008 R2 manquant dans l'Assistant Script