Moving tables with a certain prefix to a new database

Question

I have a bunch of tables in a sql server database (all with a certain prefix, e.g. ABC_table1), and I want to move ALL tables with this prefix to another database.

Is there a way in which this can be done?

I am using SQL Server 2k5.

Thanks

Answer 1

Figure out the uid of the ABC schema:

SELECT * from sys.schemas

Then substitute the schema_id for the 5 (uid) in the script below and run it:

SELECT 
    'SELECT * INTO OtherDB.ABC.' + name +
    ' FROM ABC.' + name
FROM 
    sysobjects 
WHERE 
    xtype = 'U' 
    AND uid = 5

Then Cut and paste the resulting results and run that. Alternatively create a cursor and sp_execute each row.

Answer 2

Not sure how much the terminology has changed in 2k5, but here's the instructions in 2k terminology. Just create your new database and perform an export from your old database to the new one. In the export wizard, you want to transfer objects and data. When selecting your "objects" select all tables that begin with ABC_, this is easy since they are listed alphabetically. And then export.

Unless this is something that you want to do on a regular basis (I can't see why), then I think this would probably be the easiest solution for a one time operation.

Answer 3

Thanks guys!