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.
| Author |
Topic |
|
sql2010
Starting Member
6 Posts |
 Posted - 2010-04-14 : 12:18:55
|
| I have several tables in my schemas with identity columns. In the application code, there are several places where these values are inserted by giving explicit values in the insert statement for the tables. so I am trying to say set identity_insert tablename on for each table before doing the insert. Is there a way to do the identity_insert to on for each table permanently (say, as part of the schema creation) so I don't have to do this before each insert statement in the code? Also, it looks like only one table can have this identity_insert set to on. Is this true for SQL server 2005? Any thoughts on how to handle this is much appreciated. Thanks! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-04-14 : 12:53:52
|
If you dont want to use the identity column why not just remove that property and make it a regulat int column? quote:
Also, it looks like only one table can have this identity_insert set to on. Is this true for SQL server 2005? Any thoughts on how to handle this is much appreciated.
that restriction is limited to a session.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-14 : 13:42:18
|
| The IDENTITY_INSERT ON setting remains in place for a table until you explicitly turn it off. but ... it can only be ON for one table at any one time.If you want IDENTITY attribute on a column some of the time then you will need to use IDENTITY_INSERT ON explicitly on the table when you do NOT want it.Alternatively remove the IDENTITY attribute from the column and allocate the ID yourself (when you do not have an explicit value) using MAX(MyColumn)+1 - but you will not be able to, easily, determine what value has been allocated (which you can do with IDENTITY attribute using SCOPE_IDENTITY() |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
how to set identity_insert to on permanently?