Merge mulitple databases into single database with multiple schemas in SQL Server 2008

Question

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.

Answer 1

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.

Answer 2

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).

Answer 3

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?

Answer 4

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

SELECT * INTO [NEW_DATABASE].dbo.[TABLE]
FROM [OLD_DATABASE].dbo.[TABLE]

Answer 5

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

SELECT * 
  INTO [NEW_DATABASE].dbo.[TABLE]
  FROM [OLD_DATABASE].dbo.[TABLE]

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