Migrate data from multiple disparate databases into 1

Question

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?

Answer 1

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;

Answer 2

Within the SQL Server Import and Export Wizard, select the tick box next to Source on the Select Source Tables and Views page. Once selected, click Edit Mappings. This will bring you to a Transfer Settings dialog box that allows you to Enable identity insert.