I have five smallish databases all within a single SQL Server 2008 instance. The databases are related (by business entity but not by data) so I'd like to merge them into a single database and give each its own schema within that database.

Actually, I'd like the main database to keep the dbo schema. Originally I started by creating the schemas in the main database, then scripted the tables in the first database I want to move to create the structure, and then ran insert/select queries to migrate the data. I'm okay with this but it's time consuming and I haven't even gotten to the stored procedures yet. Plus one of the databases relies on the record IDs and simply doing an insert/select will throw them off.

Surely there's an easier way to do this.

I'm not a DBA but I'm comfortable working with queries and such in SSMS. I'm working on a dev server and I'm the only one who updates the data, so I can always restore from the production server if something goes wrong. Any pointers would be much appreciated.

5 Answers 5


I've found a solution and I'm going to post it here in case anyone else is looking for this in the future. I can't say it's the best way to do this, but it worked for me and was relatively painless. I'll also disclose up front that I created the new schemas in the primary database when I first asked this question (a week or two ago) so I honestly don't know if this step is necessary.

I repeated this procedure for each database.

  1. Created a unique schema name for the database.

  2. Ran the following query for each table in the database:

    ALTER SCHEMA [new_schema_name] TRANSFER [table_name];

  3. Used SSMS to generate CREATE scripts for all views, stored procedures, triggers and functions. (Fortunately I had few of these, so this went quickly.) I did have to append the schema name to all table refereces (e.g. [dbo].[table_name] changed to [new_schema_name].[table_name] but a search and replace made this fast and easy.

  4. Used the SQL Server Import and Export Wizard to export tables from the database to the primary database.

  5. Ran the CREATE scripts and deleted the old database.

  • This ended up working for me as well, but I ended up having to disable all the triggers and constraints before running the Import/Export wizard. I also had to explicitly tell the wizard to include triggers and use identity insert. Sep 22, 2014 at 22:04

Have a look at RedGate's SQL Compare. It can probably handle what you're looking for, or at least get you a lot closer. It's not free, but you can evaluate it to see if it'll work or not. IMHO it's well worth the $$ (as are all of their tools).


As far as the ids, there is a statement to set the id. Reseed identity I believe. I second the redgate compare/ data cpare bundle - rocks.

I don't understand why you would do this though. This will make you backup and restore all objects at the same time. You're also going to have to refactor your app code as well. What benefits do you see from doing this?

  • The databases are for a series of personal web sites on my employer's server. It will make things easier for me to maintain. I originally had it set up like this a few years ago, then moved to a paid host that wouldn't allow the schemas so I had to split everything up. I would prefer being able to backup and restore everything in one shot. Nov 8, 2009 at 2:48

I recently had to (chose to) merge two small databases that had no meaningful overlap into one database. The destination table in the target database did not exist pre-query.

I did something similar to what is described the answer provided by OMG Ponies

  • note: this does retain identity specifications and also any 'gaps' in the keys. ti doesn't however transfer indexes including PK
    – Simon
    May 3, 2017 at 21:38

Being that there's only one SQL Server instance, and assuming there's no overlap - you could use:


The table in the new database can't exist before you run the statement, but it will bring over the data.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .