How to copy Some Tables or all Tables to Destination Database in SQL Server - SQL Server: Tips and Tricks - 2 (2017)

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