Reputation: 65
Copy Table Data from Different Server DB to Different Server DB
I have table structure in one Server DB and i want to copy data to another Server DB table. How to achieve it ?
Note: (Failed Cases)
Attempt 1: I tried Backup and restore, it failed due to versioning issue (10.50.2500 not matching with 10.00.4064).
Attempt 2: Export and Import, with validation error and etc...no copy from source to destination
Attempt 3: (failed due to different servers)
INSERT INTO [DB_NAME]..[dbo].[TABLE_NAME]
SELECT * FROM [DB_NAME]..[dbo].[TABLE_NAME]
(instead of above syntax i used like this also it have parsing error)
INSERT INTO [SERVER_NAME].[DB_NAME]..[dbo].[TABLE_NAME]
SELECT * FROM [SERVER_NAME].[DB_NAME]..[dbo].[TABLE_NAME]
Upvotes: 2
Views: 182
Answers (2)
Reputation: 122042
There are many ways to do this -
1. Generate CVS by this script and do export data (it's working for any table structure):
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]
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 by this script and insert data (it's working for any table structure):
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]
Output:
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. Use any data comparator like: dbForge Data Compare for SQL Server
4. Use the linked servers: MSDN
Upvotes: 1
Reputation: 24144
You can set a source DB as a Linked Server on a destination DB
Then just execute on the destination DB:
SELECT * INTO [TABLE_NAME] FROM [SOURCEDB]..[dbo].[TABLE_NAME]
This command creates table structure and inserts all rows from source to destination.
If you need to transfer Indexes, constraints, .... you can use a "Generate Script" Enterprise Manager interface command in MS SQL server.
Also on SQL 2008 you can use generate script interface command with data included. So all you need is to generate script on source DB for your table(s) and run this script on the destination DB. Here are some links:
SO: In SQL Server 2008 R2 script data missing on Script Wizard
Upvotes: 0
Related Questions
- How I can copy a table in an other server with SQL code
- How to copy database tables from one db server to another db server
- how to copy table from one server to another in SQL Server?
- copying SQL server DB table from one server to another
- Copy one table from a server to another server using query MS SQL
- How do I copy data from one table to another table on another server?
- SQL query :Copy one table to another from different servers
- Copy table data from one server to other server
- Copy table from one server to another using select statement
- How to copy table and data from one database to another database using SQL Server