Clever Geek Handbook

Moving tables with a specific prefix to a new database

I have a bunch of tables in a sql server database (all with a specific prefix, like ABC_table1) and I want to move ALL tables with that prefix to another database.

Is there a way that this can be done?

I am using SQL Server 2k5.

thank

0


source to share


2 answers


Find out the uid of the ABC schema:

SELECT * from sys.schemas

Then replace schema_id for 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 we cut and paste the results and run them. Alternatively create a cursor and sp_execute of each line.

+1


source


Not sure how much the terminology has changed in 2k5, but here are instructions for 2k terminology. Just create a new database and export from the 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 start with ABC_, this is easy as they are listed alphabetically. And then export.



If this is not what you want to do on a regular basis (I don't understand why), then I think this is probably the easiest solution for a one-off operation.

0


source






More articles:


All Articles