How to copy Some Tables or all Tables to Destination Database in SQL Server - SQL Server / TSQL Tutorial

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 . Here is the link how to create link 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 = 'TechBrothersIT'

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