SQL Server – Copy Tables from sys.tables to New Database

sql server

I have a DB with thousands of tables.

I want to copy all tables with name like 'TableType1%' to a new DB.

I can easily get a list of the tables:

select * from sys.tables t where schema_name(t.schema_id) = 'S1' AND
[name] LIKE 'TableType1%'

But how do I copy them to a new DB?

I CANNOT do this manually, as there are too many tables.

I would like to use

INSERT INTO

type statement, but don't know how to put it into select statement above.

Something like:

select * from sys.tables t 
insert int [NewDB].[S1].[ t.name ] <<< What should go here?
where schema_name(t.schema_id) = 'S1' AND [name] LIKE 'TableType1%'

Best Answer

If you are looking for a quick one-off solution, you can try this.

In the source database, run this command (making the necessary changes for TrgDb for your target database and SrcDb for your source database. Also change the where clause to pick your tables.

SELECT 'select * into TrgDb.S1.' + t.NAME + ' from  SrcDb.S1.' + t.NAME + ';'
FROM sys.tables t
WHERE NAME LIKE 'blah%';

Take the results and paste the commands into a new SSMS window that points to the target database.

Related Question