sql server 2008 - Insert into table with auto-increment field -

i have 2 tables called hrdata , hrdatahistory. hrdatahistory has same structure hrdata except first column autoincrement field , last column datetime field.

hrdata has trigger:

create trigger [hr].[hrdata_history] on [hr].[hrdata] after insert, update       insert hr.hrdatahistory     select *, getdate()      inserted ;  go 

this working on existing development machine. trying mirror relationship on local sql server instance can test changes. using ssms used 'script table create to...' , created structure of each table , index on local sql server instance. when trigger following error:

an explicit value identity column in table 'hr.hrdatahistory' can specified when column list used , identity_insert on. 

i know preferred method specify columns, want mirror production not , further want understand why working in production not on test database.

you're getting error because you're trying insert data identity column, auto-populates whenever insert row in table.

off top of head, can below (although believe there more elegant solutions , i not guarantee safe solution, nor have tried , recommend testing on test database before trying in production/live):

• add column hrdatahistory table not have identity set on (because cannot remove identity form colum once set), must have same datatype current id (identity) column

• use update query move of id's identity column new column:

  update hrdatahistory set new_column = id 

• drop identity column (but might have grave implications if have fk set on , possibly other objects use it):

  alter table hrdatahistory drop column id 

• rename "new_column" name of previous identity column:

  exec sp_rename 'hrdatahistory.new_column' , 'id', 'column' 

at point believe can use trigger "copy" newly inserted data hrdata table hrdatahistory, since column names should match , there no more conflict due identity.

again, might (not guaranteed) work i recommend first check on test environment.