Copy Specific tables from one database to another database on the same server

Question

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.

Answer 1

There are a few possible options to this. It kind of depends on your requirements I guess.

First of all it depends on your table definitions, indexes, keys and constraints.

If your table is just a simple heap and you just want to create a copy of the data you could use

drop table mytable;
select * from [databasename].[schemaname].[mytable] into mytable;

If your table is an actual copy including indexes you could use

truncate table mytable;
insert into mytable select * from [databasename].[schemaname].[mytable] ;

That obviously wouldn't work if you have foreign key constraints on mytable and the constraints don't resolve. Then you would need to update all related tables first or disable they constraints and enable/recheck them if you don't have any extra records in the referenced tables.

The import and export wizard suggestion isn't excluded because you can choose to save the result as a package and you could call that package from a script using dtexec.exe

Answer 2

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.