Inserting rows into a table with one IDENTITY column only [duplicate]

Question

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!

Answer 1

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

Answer 2

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