sql server - Are there any alternative to insert explicit value for identity column than setting IDENTITY_INSERT ON -

i have 2 tables. both have same column name. expanding purpose need migrate data 1 table table. wrote following query:

insert table1(primarykey,column1, column2) select *  table2 (<condition>) 

which causing error:

cannot insert explicit value identity column in table 'table1' when identity_insert set off

when google it, people have mentioned use

set identity_insert [spider3].[table1] on  

but there drawbacks mentioned that. though feature valid connection, unable have identity_insert thing on more 1 table. i'm looking alternatives.

can suggest alternative solutions? can use 'as' keyword in query self resolve problem? if how?

these insert statements calculating in possibility of identity insert. these can run in batch , perform identity_insert dynamically. use script generate statements.

if (objectproperty(object_id('[production].[scrapreason]'), 'tablehasidentity') = 1) set identity_insert [production].[scrapreason] on; insert [adventureworks2014].[production].[scrapreason] ([scrapreasonid], [name], [modifieddate]) select * [adventureworks2014].[production].[scrapreason]; if (objectproperty(object_id('[production].[scrapreason]'), 'tablehasidentity') = 1) set identity_insert [production].[scrapreason] off;  if (objectproperty(object_id('[humanresources].[shift]'), 'tablehasidentity') = 1) set identity_insert [humanresources].[shift] on; insert [adventureworks2014].[humanresources].[shift] ([shiftid], [name], [starttime], [endtime], [modifieddate]) select * [adventureworks2014].[humanresources].[shift]; if (objectproperty(object_id('[humanresources].[shift]'), 'tablehasidentity') = 1) set identity_insert [humanresources].[shift] off;  if (objectproperty(object_id('[production].[productcategory]'), 'tablehasidentity') = 1) set identity_insert [production].[productcategory] on; insert [adventureworks2014].[production].[productcategory] ([productcategoryid], [name], [rowguid], [modifieddate]) select * [adventureworks2014].[production].[productcategory]; if (objectproperty(object_id('[production].[productcategory]'), 'tablehasidentity') = 1) set identity_insert [production].[productcategory] off;  

in case select , insert both happen [adventureworks2014] not case, had 1 db avail demonstrate. if you're interested in how generate those, see below.

declare @v_sourcedatabase      nvarchar(250);  declare @v_destinationdatabase nvarchar(250); declare @t_tablestoprocess     table (fulltablename nvarchar(250),                                       tablename     nvarchar(250),                                       tableschema   nvarchar(250),                                       columnlist    nvarchar(max)); declare @v_columnlist         nvarchar(max); declare @v_tablename          nvarchar(250); declare @v_tableschema        nvarchar(250);  -- -------------------------------------- -- -- set source & destination database here -- -- -------------------------------------- --  set @v_sourcedatabase      = n'adventureworks2014'; set @v_destinationdatabase = n'adventureworks2014';  -- generate tables process insert @t_tablestoprocess select concat(quotename(syssch.name), '.', quotename(systab.name)) fulltablename      , systab.name      , syssch.name      , null   sys.tables systab inner join sys.schemas syssch   on syssch.schema_id = systab.schema_id type = 'u';  declare tablelist cursor fast_forward   select tablename, tableschema   @t_tablestoprocess;  open tablelist; fetch next tablelist @v_tablename, @v_tableschema;  while @@fetch_status = 0 begin   select @v_columnlist = concat(@v_columnlist, quotename(column_name), ', ')   information_schema.columns    table_name = @v_tablename     , table_schema = @v_tableschema    set @v_columnlist = left(@v_columnlist, len(@v_columnlist) - 1);    update @t_tablestoprocess   set columnlist = @v_columnlist   tablename = @v_tablename     , tableschema = @v_tableschema;    set @v_columnlist = '';      fetch next tablelist @v_tablename, @v_tableschema; end   close tablelist; deallocate tablelist;  -- generate insert statements select concat(n'if (objectproperty(object_id(''', fulltablename, '''), ''tablehasidentity'') = 1) set identity_insert ', fulltablename, n' on; '             , n'insert ', quotename(@v_destinationdatabase), '.', fulltablename             , n' (', columnlist, n')'             , n' select * ', quotename(@v_sourcedatabase), n'.', fulltablename, n';'             , n' if (objectproperty(object_id(''', fulltablename, '''), ''tablehasidentity'') = 1) set identity_insert ', fulltablename, n' off; ') @t_tablestoprocess; 

small remark: wrote script without pulling through real testing. things can improved. way handled building of column names less ideal. using xml path & stuff better option:

select chars = stuff((     select ', ' + [char]     dbo.chars xml path(''), type).value('.', 'varchar(max)'), 1, 2, '')  

it's nudge in possible direction. if ever find time i'll rework more fool proof / generic solution.

as way identity inserts without using identity_insert, doubt there any.