SQL Server – How to Copy Specific Tables Between Databases on the Same Server

sql server

I have 2 databases that are identical. I make a backup of DB1 and restore as DB2.
2 days later I want to copy 5 tables from DB1 into DB2. All tables start with 'AG'. Is there a simple way of doing this? I would like to do this in a T-SQL script so I can do this often and easily.

Best Answer

Below script will help you :

set nocount on
/*

Note : This will just insert data and create a basic structure of the destination table.
        It wont carry any PK, FK or indexes on the destination table.

        If you know that you always want the data from certain tables from source,
        then its better to create them in destination along with PK, FK and indexes
        and then use below method (JUST change `select * into` to `insert into `

   convert below to dynamic sql if you want to automate it once you are happy !

*/

declare @sourceTable sysname
declare @sourceSchema sysname
declare @sourceDB sysname

declare @destTable sysname
declare @destSchema sysname
declare @destDB sysname

set @sourceDB = 'DB1'
set @destDB = 'DB2'

select 'select * into '+QUOTENAME(@sourceDB)+'.[dbo].'+QUOTENAME(name)+ char(10)+
        ' from '+QUOTENAME(@destDB)+'.[dbo].'+QUOTENAME(name)+';' + char(10)
from sys.tables
where name like 'AG%' -- since your table name start with AG

Another method would be to script out schema and then use BCP OUT and BULK INSERT INTO the destination database. BCP method will be much faster when you have large number of data to move.

Other alternatives are SSIS (when you have to repeatedly do the same task often) or PowerShell.

Make sure to empty (delete rows if the tables have foreign key relationship or truncate them if they dont have). Be careful with identity fields when you use delete or truncate.

Related Question