How to Migrate Data from Multiple Disparate Databases into One

migrationsql server

I have a SQL Server instance with 5 different databases. For a few reasons, we would like to combine these into just 1 database, separating out each database into it's own schema. I've built the schema/tables into the 'master' database already, but i'm having trouble building a good way to import the data from the old databases into the new master one. something like:

insert into [masterDB].[oldDBSchema].[tableName] 
select * from [oldDB].dbo.[tableName]

does not work, because of this error:

An explicit value for the identity column in table
'cobraclientdata.cconfigd.adjudicationbutton' can only be specified
when a column list is used and IDENTITY_INSERT is ON.

now i can set IDENTITY_INSERT to ON easily enough, but i'd rather not specify the columns if i don't have to because it would be a PITA to do that for every table. These tables have exactly the same structure, is there a quick way to do this (I'm trying to build a script that i can run this 'update' on several servers), or am i stuck writing out the columns for each table, or doing an import/export data explicitly?

Best Answer

You can generate a list of T-SQL statements using dynamic SQL, something like this:

DECLARE @srcDB sysname;
DECLARE @newDB sysname;
DECLARE @oldSchema sysname;
DECLARE @newSchema sysname;

SET @srcDB = 'oldDB';
SET @newDB = 'masterDB';
SET @oldSchema = 'dbo';
SET @newSchema = 'newDBSchema';

SELECT N'INSERT INTO ' + QUOTENAME(@newDB) + N'.' + QUOTENAME(@newSchema) + N'.' + QUOTENAME(t.name) + N'(' + STUFF((SELECT N', ' + QUOTENAME(c.name) FROM sys.columns c WHERE c.object_id = t.object_id ORDER BY c.column_id FOR XML PATH(N'')), 1, 2, N'') + N'
) SELECT ' + STUFF((SELECT N', ' + QUOTENAME(c.name) FROM sys.columns c WHERE c.object_id = t.object_id ORDER BY c.column_id FOR XML PATH(N'')), 1, 2, N'') + N'
FROM ' + QUOTENAME(@srcDB) + N'.' + QUOTENAME(@oldSchema) + N'.' + QUOTENAME(t.name)
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = @oldSchema
ORDER BY t.name;
Related Question