Alex Rivera | Logout

Inserting rows into a table with one IDENTITY column only

Asked 2010-11-07T12:22:37.750
86

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!

sql sql-server t-sql stored-procedures
Edit
Report

2 Answers

142

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
answered 2010-11-07T12:42:32.213
1

You need to add the IDENTITY_INSERT to your select statement:

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable
(AdminCol)

SELECT AdminColValue

 FROM Tableb

When you're done, make sure you remember to 

SET IDENTITY_INSERT MyTable OFF

Here's a good description of how it works from BOL: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx

answered 2010-11-07T12:28:03.140

Your Answer