Copy tables selected from sys.tables to new DB 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 ] &lt;&lt;&lt; What should go here?
where schema_name(t.schema_id) = 'S1' AND [name] LIKE 'TableType1%'
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.