Copy tables selected from sys.tables to new DB

Question

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%'

score 0 · Accepted Answer · answered Sep 25 '19 at 14:41

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.

score 0 · Answer 2 · answered Sep 25 '19 at 15:04

You're going to be using dynamic SQL. And if that sounds scary don't worry, it's really not. First though I would recommend scripting out the tables you want to put in the new database seperately rather than using a SELECT INTO. This way you will get all of the indexes, triggers, etc scripted along with you. The easiest way to handle that is going to be using the Object Explorer Details window in SSMS (Management Studio). Basically it will let you sort and/or filter so you can select the tables you want to copy and then right click and script the whole thing out. I have a set of instructions (and a short demo gif) here. Then you run the script on the new database.

That said, here is how you would copy the data. This does assume that the database is on the same server. If it's not you'll need to use something like BCP or SSIS. If it is then you can code out the INSERT INTOs. Here is a first stab at it:

SELECT -- Create the insert command
    -- If the table has an identity turn identity insert on
    CASE WHEN OBJECTPROPERTY(tables.object_id,'TableHasIdentity') =0 THEN '' ELSE
        'SET IDENTITY INSERT RemoteDB.' + schemas.name + '.' + tables.name + ' ON; 
' END +
    -- Generate the INSERT INTO command
        'INSERT INTO RemoteDB.' + schemas.name + '.' + tables.name + ' (' + MyColumns.List + ')
        SELECT ' + MyColumns.List + ' FROM LocalDB.' + schemas.name + '.' + tables.name + ';' + 
    -- If the table has an identity turn identity insert off
        CASE WHEN OBJECTPROPERTY(tables.object_id,'TableHasIdentity') =0 THEN '' ELSE
        '
SET IDENTITY INSERT RemoteDB.' + schemas.name + '.' + tables.name + ' OFF; ' END
FROM sys.tables 
JOIN sys.schemas
    ON tables.schema_id = schemas.schema_id
-- Generate a comma delimited list of columns for each table
CROSS APPLY (  SELECT 
            STUFF((SELECT ', ' + columns.name  
                FROM sys.columns
                WHERE columns.object_id = tables.object_id
                ORDER BY columns.column_id
                FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
            , 1, 2, '') AS list ) MyColumns(List)
WHERE 
    -- Filter just the tables you want
    tables.name like 'Filter%'
    -- Make sure that they don't already exist in the remote location
  AND NOT EXISTS (SELECT 1
                    FROM RemoteDB.sys.tables RemoteTables
                    JOIN RemoteDB.sys.schemas RemoteSchemas
                        ON RemoteTables.schema_id = RemoteSchemas.schema_id
                    WHERE RemoteTables.name = tables.name
                      AND RemoteSchemas.name = schemas.name)

From here you can either copy the output into a query window to run or put a cursor (or some other type of loop) and run each command one at a time. Make sure to change RemoteDB and LocalDB to the appropriate names, and it's designed to be run on the source/local DB.

score -1 · Answer 3 · answered Sep 25 '19 at 13:55

Do you want to copy all of the data inside of them to the new DB, or are you looking to have blank copies of the table in the new DB?

You should be able to do a Tasks -> Export Data to the destination DB if you need the data, otherwise you could use the SSMS Generate Script feature to copy the tables over with only the Schema.

Copy tables selected from sys.tables to new DB

3 Answers3