SQL Server: Tips and Tricks - 2 (2017)
How to copy Some Tables or all Tables to Destination Database in SQL Server
Scenario:
I got an email from one of the viewer who would like to copy some tables from a source database to destination database. If new tables are added, the process should copy those tables as well. As per his requirement if the tables already exists in destination database, he need to drop it and create new one as definition in source database for tables can change.
Solution:
There can be multiple solutions for this problem, today we are going to solve this by using TSQL Cursor. In our Scenario we assumed that the both database are on same servers. In your situation you might have the Databases on different servers. You will be creating linked server.
The only thing you will change in the below script add linked server before @DestinationDB . You can hard code or create a variable and add in the script.
The below code will drop existing tables in destination and then create new table and copy the data from source database table. Make sure you test the code in development before run in QA, UAT and Production.
USE TestDB1 --Your SourceDB
GO
DECLARE @DestinationDB VARCHAR(100)
SET @DestinationDB = 'TechIT'
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE Cur CURSOR
FOR
--Choose the list of tables you would like to copy to
--New Destination Database
SELECT DISTINCT Table_Schema AS SchemaName
,Table_Name AS TableName
FROM information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN Cur
FETCH NEXT
FROM Cur
INTO @SchemaName
,@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DropTableSQL NVARCHAR(MAX)
--Drop the Table in Destination if exists
SET @DropTableSQL = '
if exists (select * from ' + @DestinationDB + '.INFORMATION_SCHEMA.TABLES where
TABLE_NAME = ''' + @TableName + ''' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
drop table [' + @DestinationDB + '].[' + @SchemaName + '].[' + @TableName + ']'
PRINT (@DropTableSQL)
EXEC (@DropTableSQL)
DECLARE @InsertSQL NVARCHAR(MAX)
--Create new table and move data to it from Source Table
SET @InsertSQL = 'SELECT * INTO ' + '[' + @DestinationDB + '].[' + @SchemaName + '].[' + @TableName + ']
FROM [' + @SchemaName + '].[' + @TableName + ']'
PRINT @InsertSQL
EXEC (@InsertSQL)
FETCH NEXT
FROM Cur
INTO @SchemaName
,@TableName
END
CLOSE Cur
DEALLOCATE Cur
Things we learnt from this code
· How to get list of tables with Schema from a database
· How to use Cursor to loop through records in TSQL
· How to use Dynamic SQL to prepare our query for each row and execute
· How to copy tables from one database to another database on same server or on linked server