Phil Phil - 1 year ago 77
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 Source

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

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


  • 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download