Using set IDENTITY_INSERT with exec

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using set IDENTITY_INSERT with exec

Author

Topic

stewart.fay
Starting Member

2 Posts

Posted - 2008-12-04 : 16:43:16

Set @sTable = 'Sales'
--Set the Identity insert on
Set @sSql = 'set IDENTITY_INSERT ' +rTrim(@sTableName)+' On'
Exec (@sSql)

I wish to use something like above to the identity on

then insert into the given table and then set the insert_identity off, so that I can do the same with the next supplied table.

Any ideas why this is not actually working and I get the message:
"Cannot insert explicit value for identity column in table 'Sales' when IDENTITY_INSERT is set to OFF."

Please let me know if you need anymore info. I'm trying to copy one set of table data from one server to another but been dynamic.

Thanks
Stew

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-04 : 16:51:58

The set identity_insert statement is valid only in the scope of the exec. If your insert statement is outside then you will receive this error.

You will need to put the insert and subsequent set identity_insert off statements within the same dynamic sql exec.

Please post back if you need help with that.

Nathan Skerl

stewart.fay
Starting Member

2 Posts

Posted - 2008-12-05 : 04:24:26

Thank you my aged yak warrior. it worked perfectly and now I dynamically populate 200 tables in just 53 lines!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources