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!

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

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

@Phil: Don't you mean your table has two(2) columns, the autoincrementing PK column and an AdminName column? If it only has one column where the AdminName goes, the AdminName is the PK and you cannot autoincrement a string, of course. Do the business rules expect you to make a fully-qualified Windows username the primary key? That would be viable and make sense, because then you wouldn't need an alternate unique index on the AdminName column.

But if your table has two columns, not one:

In SQLServer the autoincrement is part of the table/column definition. You define the column as an integer and then also make it an identity column, specifying the increment, usually 1, but it could be 2 or 5 or 10 or whatever. To insert a row, you simply insert the other column(s) value(s) and do nothing with the PK column:

insert into T
(foo)   -- column(s) list
values('bar') -- values list

Your stored proc that does the insert can make SCOPE_IDENTITY a RETURN value or SCOPE_IDENTITY can be passed back to the client as an OUT parameter.

P.S. SCOPE_IDENTITY() returns the most recently generated autoincremented identity value in the current scope; it does not generate the next identity value.

EDIT:

Presumably, your Administrators table contains a set of administrators. But if it has no columns whatsoever other than the integer primary key column, there is no way to identify the administators; the only thing you can do is distinguish them from each other. That doesn't get you very far at all. But if your Administrator table had either of the following structures:

ID   INTEGER PRIMARY KEY AUTOINCREMENT
windowsusername   varchar(50)  (unique index)

OR

windowsusername varchar(50) primary key

you would be able to reference the Administrator's table from other tables, and the foreign keys would be MEANINGFUL. And that's precisely what a table consisting of a single integer column lacks -- meaning.

Having two columns, you could then have a stored procedure do this:

insert into Administrators
(windowsusername)
values('mydomain\someusername');
return SCOPE_IDENTITY();

and your client-program would get back as a return value the autoincremented id that had been autogenerated and assigned to the newly inserted row. This approach is the usual practice, and I would go so far as to say that it is considered "best practice".

P.S. You mention that you didn't know how to "insert a value" if you "didn't have anything to insert". There's a contradiction there. If you have nothing to insert, why insert? Why would you create, say, a new CUSTOMER record if you know absolutely nothing about the customer? Not their name, their city, their phone number, nothing?