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.