Phil Phil - 5 months ago 9
SQL Question

Inserting rows into a table with one IDENTITY column only

I have a table Administrator with only one column, adminId which is the primary-key. Because of business rules it has to be this way.

I'd like to understand once and for all how I can write stored procedures that insert values in tables like this. I am using SQL Server and T-SQL and tried using SCOPE_IDENTITY() but that doesn't work since the table has INSERT_IDENTITY to false or off.

I'd really like to not insert a dummy value just to be able to insert a new row. Thanks!

gbn gbn
Answer

If you have one column that is an IDENTITY, just do this

INSERT MyTable DEFAULT VALUES;  --allows no column list. The default will be the IDENTITY
SELECT SCOPE_IDENTITY();

If you don't have identity, then can you set it? This is the best way.. and use the SQL above.

If not, you want to insert a new row

INSERT MyTable (admidid)
OUTPUT INSERTED.admidid --returns result to caller
SELECT ISNULL(MAX(admidid), 0) + 1 FROM MyTable

Notes:

  • Under high loads the MAX solution may fail with duplicates
  • SCOPE_IDENTITY is after the fact, not before
  • SCOPE_IDENTITY only works with an IDENTITY column. Ditto any idiocy using IDENT_CURRENT
  • The output clause replaces SCOPE_IDENTITY for the MAX solution