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

21,125

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.

Share:
21,125

Related videos on Youtube

Artur Peniche
Author by

Artur Peniche

Updated on September 17, 2022

Comments

  • Artur Peniche
    Artur Peniche over 1 year

    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.

  • StriplingWarrior
    StriplingWarrior over 9 years
    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.
  • Jerad Rose
    Jerad Rose about 7 years
    note: this does retain identity specifications and also any 'gaps' in the keys. ti doesn't however transfer indexes including PK