Inserting rows into a table with one IDENTITY column only

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!

Solution 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

Solution 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

Comments

[ZygD]

To clarify: your question is "how to insert rows in a SQL Server table with a single IDENTITY column"?

[Phil]

Yes, you are right, thanks for clarifying

[BJury]

For people landing here, this has been asked before and the correct answer is here: stackoverflow.com/questions/850327/…

[Phil]

Am I supposed to do it like this: SET IDENTITY_INSERT Administrator ON INSERT INTO Administrator (SCOPE_IDENTITY()) SET IDENTITY_INSERT Administrator OFF ?

[Phil]

My table has ONE column, adminId, which is an incremental int value

[Tim]

That is the strangest design I've ever seen, and I've been in the business for over 20 years and have seen some strange things in my day. So your question is, How do you insert into a table that contains only a primary key column defined as an autoincrementing identity column, when the table contains that PK column and only that PK column, no other columns..

[DataWriter]

Yes. That's all you need to do. SET ON, write your code, SET OFF at the end.

[Tim]

So what's the downvote for? A factual error somewhere or for expressing an opinion about the lack of merit of the design?

[Phil]

Yes, the design is strange, I agree. It is probably because this table will expand in the future. I downvoted because you answered how to do it with two columns and not one. My problem was that I didn't know how to insert a value when I didn't have anything to insert. If you feel I've misunderstood your answer; please explain and I'll upvote if I understand what you mean.

[Tim]

OK, I will append to my previous answer since the site is not letting me add another answer.

[Phil]

Just to clarify the reasoning behind the DB-design. We have users in one table. If there is a has-relation between users and administrators; they are treated as admins. There is nothing more to keep in the Admin-table than the actual unique id. The "real" data is in the user-table and the has-relation (with timestamps and who granted etc).

[Tim]

@Phil: So you're using a table and a foreign key relationship to express what a simple boolean/bit column could do. You may not be able to add such a bit column to your table and have to come up with some workaround. But an autoincrementing integer whose autoincrementation must be disabled is a poor solution. All you need is a parallel table with a one-to-one relationship to the users table and in that table you create a bit column. When that column is true, it means the user has-admin. Clean. Standard. Design.

[Mmm]

I don't think this is the right answer. If it's an identity column, you shouldn't be adding values to it. The accepted answer is the correct answer.