1

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?

  • 1
    Specifying the list of columns is a PITA? You know you can drag the list of columns from the Columns node in SSMS > Object Explorer, right? – Aaron Bertrand Mar 16 '18 at 18:48
2

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;
  • 1
    Lovely, thank you. Exactly what i was hoping for. Had to edit a little bit to add a 'SET IDENTITY_INSERT ' to it, but works great. – Phil Mar 16 '18 at 21:37
1

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.

Select Source Tables and Views

Transfer Settings

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.