[ACCEPTED]-Inserting rows into a table with one IDENTITY column only-stored-procedures

Accepted answer
Score: 141

If you have one column that is an IDENTITY, just 4 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 3 can you set it? This is the best way.. and 2 use the SQL above.

If not, you want to insert 1 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
Score: 1

You need to add the IDENTITY_INSERT to your 3 select statement:

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable
(AdminCol)

SELECT AdminColValue

 FROM Tableb

When you're done, make 2 sure you remember to

SET IDENTITY_INSERT MyTable OFF

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

Score: 0

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

But if your table has two columns, not 42 one:

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

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

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

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

EDIT:

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

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

OR

windowsusername varchar(50) primary key

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

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

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

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

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

More Related questions