Copy Specific tables from one database to another database on the same 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.
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