0

How to Use T-SQL to Copy Contents of Tables Between Two Databases that have the same Schema

Emeka OkekeDec 19, 2019

This post will show you how to use TSQL to copy contents of one table to another between two databases with the same schema. The desired code should be able to copy data in this format:

Insert into destinationDB.schema.table (col1,col2,col3,col4..etc)
Select (col1,col2,col3,col4..etc) from  SourceDB.schema.table (col1,col2,col3,col4..etc)

How do we accomplish this task?

The T-SQL Script will only copy tables containing rows without any errors. The code will DYNAMICALLY generate statements in the above stated format considering things like ORDER OF INSERTS, ETC):

The script should handle any table relations, or other constraints to make destination tables copy the exact set of rows from the source tables. The use of temp tables is not allowed.

First we will run the following code in SQL Server Management Studio (SSMS) to create the necessary schema with sample data. The data will be copied from database SOURCE tables to database DESTINATION tables.

First we create the sample source and destination databases:


CREATE DATABASE SourceDB
GO
CREATE DATABASE destinationDB
GO

Next we create the sample schemas on the created databases:


USE SourceDB
GO
CREATE SCHEMA Test
GO
USE destinationDB
GO
CREATE SCHEMA Test
GO

Next we create the tables to be used for this tutorial in both sourceDb and destinationDB databases:


CREATE TABLE Test.SUBSCRIPTIONS (
subscription_id INT IDENTITY(1,1) NOT NULL, 
user_id INT NOT NULL,
subscription_type CHAR (50) NOT ULL, 
active_indicator CHAR (3) NOT NULL
CONSTRAINT PK_subscription_id PRIMARY KEY CLUSTERED
(subscription_id ASC))

CREATE TABLE Test.USERS (
User_id int IDENTITY(1,1) NOT NULL, 
Email CHAR(50), 
Gender CHAR(1), 
Age int, Name CHAR(60)
CONSTRAINT PK_users PRIMARY KEY CLUSTERED
(user_id ASC))

CREATE Table Test.TRANSACTIONS (
subscription_id INT,
action CHAR(20),
timestamp DATE
CONSTRAINT PK_transaction PRIMARY KEY CLUSTERED
(subscription_id, action, timestamp ASC))

CREATE TABLE Test.cancellations 
(subscription_id INT, user_id INT)

Next we define the table constraints



ALTER TABLE Test.SUBSCRIPTIONS WITH CHECK ADD CONSTRAINT FK_users FOREIGN KEY(user_id)
REFERENCES Test.USERS (User_id)

ALTER TABLE Test.SUBSCRIPTIONS CHECK CONSTRAINT FK_users

ALTER TABLE Test.[SUBSCRIPTIONS] ADD UNIQUE NONCLUSTERED 
(user_id ASC, [subscription_type] ASC)

ALTER TABLE test.TRANSACTIONS WITH CHECK ADD CONSTRAINT FK_SUBSCRIPTIONS FOREIGN KEY(subscription_id)
REFERENCES test.SUBSCRIPTIONS (subscription_id)

ALTER TABLE test.TRANSACTIONS CHECK CONSTRAINT FK_SUBSCRIPTIONS

Next we insert sample data in the sourceDB tables starting with the Test.Users table:


USE sourceDB
GO
INSERT test.USERS values (
'a@a.com', 'M', 30, 'Rob'),
('a@a.com', 'M', 31, 'Robert'),
('b@b.com' , 'F', 18, 'Lucie'),
('b@b.com' , 'F', 22, 'Lulu'),
('c@c.com' , 'M', 10, 'Kim'),
('c@c.com' , 'F', 18, 'Kim'),
('c@c.com' , 'F' , 8, 'Kim'),
('d@d.com' , 'F' , 18, 'JJ'),
('d@d.com' , 'M' , 22, 'Jay'),
('e@e.com' , 'F' , 88, 'Bill'),
('e@e.com' , 'F' , 88, 'Will'),
('e@e.com' , 'F' , 60, 'Will'),
('f@f.com' , 'M', 70, 'George')

Next we load the Test.Subscriptions table:



INSERT Test.SUBSCRIPTIONS values 
(2, 'Magazine', 'Yes' ),
(3, 'Music CD', 'No'),
(3, 'Magazine', 'Yes') ,
(3, 'Video' , 'Yes' ),
(8, 'Magazine', 'Yes'),
(9, 'Video' , 'Yes' ),
(10 , 'Magazine' , 'No'),
( 13 , 'Magazine', 'Yes') 

Lastly we load the Test.TRANSACTIONS table:



INSERT Test.TRANSACTIONS values 
(1, 'Renewal', '2002-09-10'),
(2, 'Renewal', '2002-Jan-01' ),
(2, 'Cancellation', '2002-Feb-01'),
(3, 'Renewal' , '2002-Aug-20'),
(4, 'Renewal', '2002-Aug-01' ),
(4, 'Renewal' , '2002-Sep-01' ),
(5, 'Renewal' , '2002-Aug-01'),
(6, 'Renewal' , '2001-Sep-01'),
(7, 'Renewal', '2002-Sep-01'),
(7, 'Cancellation', '2002-Sep-10')

TSQL Code to Copy Contents From One Table to Another

Finally we will use the below code dynamically generated as required to copy contents of one table to another. The query should be executed in the SOURCEDB database:




USE SOURCEDB
GO
DECLARE @SQL NVARCHAR(MAX)=(
SELECT REPLACE(STUFF(( SELECT '$' + QUERY FROM(
SELECT 'INSERT INTO DESTINATIONDB.' + TABLE_SCHEMA +  '.' + TABLE_NAME + '(' + 
(SELECT STUFF((SELECT ',' + '[' + COLUMN_NAME + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=A.TABLE_NAME FOR XML PATH('')),1,1,'')) 
+ ')' + CHAR(10) + 'SELECT ' + 
(SELECT STUFF((SELECT ',' + '[' + COLUMN_NAME + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=A.TABLE_NAME FOR XML PATH('')),1,1,'')) + CHAR(10) +
'FROM SOURCEDB.' + TABLE_SCHEMA + '.' + TABLE_NAME AS QUERY 
FROM (SELECT A.TABLE_NAME,A.TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES A
WHERE A.TABLE_NAME NOT IN (SELECT OBJECT_NAME(parent_object_id) FROM SYS.FOREIGN_KEYS)
AND EXISTS(SELECT 1 FROM SYS.PARTITIONS WHERE OBJECT_NAME(OBJECT_ID)=A.TABLE_NAME AND [ROWS]>0)) A
UNION ALL
SELECT 'ALTER TABLE DESTINATIONDB.' + TABLE_SCHEMA +  '.' + TABLE_NAME + ' NOCHECK CONSTRAINT ' + CONSTRAINT_NAME + CHAR(10) + 'INSERT INTO DESTINATIONDB.' + TABLE_SCHEMA +  '.' + TABLE_NAME + '(' + 
(SELECT STUFF((SELECT ',' + '[' + COLUMN_NAME + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=A.TABLE_NAME FOR XML PATH('')),1,1,'')) + ')' + CHAR(10) +
'SELECT ' + 
(SELECT STUFF((SELECT ',' + '[' + COLUMN_NAME + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=A.TABLE_NAME FOR XML PATH('')),1,1,'')) + CHAR(10) +
'FROM SOURCEDB.' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) +
'ALTER TABLE DESTINATIONDB.' + TABLE_SCHEMA +  '.' + TABLE_NAME + ' WITH CHECK CHECK CONSTRAINT ' + CONSTRAINT_NAME + CHAR(10)
AS QUERY 
FROM (SELECT A.TABLE_NAME,A.TABLE_SCHEMA, E.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLES A
INNER JOIN (SELECT C.TABLE_NAME,C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN SYS.FOREIGN_KEYS D ON C.CONSTRAINT_NAME=D.name) E ON A.TABLE_NAME=E.TABLE_NAME
WHERE EXISTS(SELECT 1 FROM SYS.PARTITIONS WHERE OBJECT_NAME(OBJECT_ID)=A.TABLE_NAME AND [ROWS]>0)) A) G
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,''),'$',CHAR(10)))

EXEC SP_EXECUTESQL @SQL

Query explanation

This query essentially used dynamic management views or DMVs INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES to get the sourcedb database's tables and their corresponding columns.

The system table SYS.PARTITIONS along with its [Rows] column is used to get the total number of rows in each table so we can get only those tables with at least one row as required.

Also we used DMV INFORMATION_SCHEMA.TABLE_CONSTRAINTS and the system table SYS.FOREIGN_KEYS to get the tables constraint so we can disable them before loading to each table on the destinationDB database.

We used the FOR XML PATH('') directive to generate $ seperated INSERT blocks. The $ is then replaced with ASCII non printable character CHAR(10) to break each INSERT block into a newline. The SQL type NVARCHAR(MAX) is used to ensure that TSQL can properly interpet the generated text including both unicode and non-unicode characters.

Finally we set the result of the SQL code to the @SQL variable and then execute it using the SP SP_EXECUTESQL with the @SQL varibale as its input parameter. If you prefer, you can also execute it directly EXEC(@SQL).

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia