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?