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 |
|
anculutza
Starting Member
4 Posts |
 Posted - 2006-09-30 : 16:11:03
|
| Anybody can help me, please? Where is kept the next value for the identity column (which is used when an insert of a new record is made in the table). In which system tables? Or?anculutza |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-30 : 16:35:45
|
Normally you "query" this value after you insert a new row, e.g.INSERT INTO MyTable(MyCol1, MyCol2, ...) VALUES ('Foo', 'Bar', ...)SELECT SCOPE_IDENTITY() AS [MyID]If you want to reset the next-number-to-be-used then you should use:DBCC CHECKIDENT ('MyTable', RESEED, 12345)Kristen |
 |
|
|
anculutza
Starting Member
4 Posts |
Posted - 2006-09-30 : 17:00:33
|
| Thanks Kristen.I also know about @@IDENTITY which is pretty much like SCOPE_IDENTITY.My problem is: when i insert values into an empty table i have to know if there were previous inserts (so the identity column will be >1). I have to know this thing before the insert.anculutza |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-30 : 17:41:41
|
"i have to know if there were previous inserts"Do you mean previous successful inserts, or the table is empty but there were previous failed inserts?For the first you can do:IF EXISTS (SELECT * FROM MyTable)BEGIN ... data exists ...END or even:SELECT MAX(MyID) FROM MyTable"I also know about @@IDENTITY which is pretty much like SCOPE_IDENTITY."Well just so long as you know that there are pretty much zero circumstances where you should use @@IDENTITY instead of SCOPE_IDENTITY()  Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-30 : 19:09:21
|
quote:
Originally posted by anculutza
My problem is: when i insert values into an empty table i have to know if there were previous inserts (so the identity column will be >1). I have to know this thing before the insert.
Only if you have a poorly designed database application.Read up on the purpose and principles of surrogate keys."Once you establish possibility, all that remains are desire and determination." |
|
|
|
|
|
|
identity columns