How to get the key that was just generated for a new row

Transact-SQL
1

I have to add a record to a table. It has an auto-generated key.

Once the record is written I have to add that key to a bridge table that connects two tables together.

That is, I have tables x, y, and z. Add record to table x. Get the auto-generated key of the last record of x. Then move that key to table z along with key to table y one already has and write them.

Thanks!

2

IDENTITY? or something-else?

For an IDENTITY column you can do:

INSERT INTO YourTable
(
    Col1, Col2, ...
)
VALUES(@Value1, @Value2)

SELECT @AssignedID = scope_identity()
       , @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT,

This only works if you are inserting a single row. If you want the ROWCOUNT and any ERROR value (0=No error) then you need to store those in the SAME Select statement (because those values are only valid for the immediately preceding statement)

3

Thanks.

I'm thinking...

SELECT nextval(pg_get_serial_sequence(x', 'id_x')) AS new_id; // will get the next value and can use it.

Catch is, this is in VS 2013 C#... so when I make the sql statement I have to figure out a way to pass the new value back to the form! Hmmm...

4

You can have something in SQL "generate" a next-sequence-value for you, but it is fraught with problems.

What happens if two people want a next-value at the same time?

What happens if you allocate a value to Person-A, and then another (different) value to Person-B but Person-A never saves their record? (this usually doesn't matter ... but if it does you can't do it that way!)

What about performance? Usually the way this works is for the nextval() / pg_get_serial_sequence() functions to either:

Store the next value. The function returns that value and then increments the value that is stored.
or
Look up the maximum value that is in use, add one, and return that (this does NOT work for two people wanting new values at the same time).

By contrast if you allocate the value when you SAVE the record you CAN "Lookup the MAX current value and add one" because the operation is ATOMic - if two people SAVE at the same time they will get in a queue, so the second person's value will be one-more-than-the-first-person's

Downside of ALL of these is that they take more "effort" than the simple solutions. They have to do a database lookup, they have to have sophisticated locking (which itself becomes a bottleneck in a busy system), and they have to do a round-trip to the SQL Server to get the next value and THEN another round trip to actually save the record.

(There is one area where this type of solution excels, and that is where your nextval() function has an extra parameter - the number of rows you will be creating. SO if you routinely add more than one row in a batch the nextval() function returns the first ID number you should use and then reserves the next N-values for you. Basically you say you want 10 values and nextval() gets the current value and increments it by 10.

But for one-by-one record creation it is not a good solution.

The better solution is to allocate the value when you save the record. In particular using the IDENTITY property, because SQL manages the "what is the next value" stuff for you. You don;t have to worry about two people getting the same value etc etc. The only, possible, downside is that if you do:

INSERT INTO YourTable ...
... do some other stuff ...
ROLLBACK

then the IDENTITY value that SQL gave YOU will then NOT be used by someone else. They will get the next-number-AFTER the one you were given (but didn't use). Thus you can get gaps in your ID numbers. Best to learn to live with that, rather than try to "fill in the gaps".

But ... that does NOT work well for batches of numbers (yeah SQL will happily allocate a unique, ascending, value for each row on a column that has the IDENTITY property if you INSERT a whole batch of rows, but it is much harder to send that information back to the caller so they can then insert some associated records in another table, using the newly allocated ID numbers from the Primary Table). For a single row its easy, for a multi-row-batch it is harder.

There are two ways to do that.

  1. Just SELECT it and "read" the resultset you get back. So your application does:
INSERT INTO YourTable(Col1, Col2) VALUES(123, 'abc')
SELECT scope_identity() AS [ThisIsYourID]

then you just interrogate the value of "ThisIsYourID" from the resultset you get back.

  1. Use an OUTPUT parameter

Call a Stored Procedure to do the actual insert. That can store the result from scope_identity() and pass it back to you in one of the parameters that you called the SProc with:

CREATE PROCEDURE SaveMyRecord
    @Col1 int,
    @Col2 varchar(10)
    @TheAllocatedID int OUTPUT
AS
INSERT INTO YourTable(Col1, Col2) VALUES(@Col1, @Col2)
SELECT @TheAllocatedID = scope_identity()

If you don't want to write Stored Procedures (i.e. you are using dynamically generated SQL in your APP) you can get the same effect using sp_ExecuteSQL:

EXEC sp_ExecuteSQL N'INSERT INTO YourTable(Col1, Col2) VALUES(123, ''abc'')
SELECT @TheAllocatedID = scope_identity()',
    N'@TheAllocatedID int OUTPUT',
    @TheAllocatedID = @TheAllocatedID OUTPUT

But if you want an Array of allocated-IDs sent back to your APP its all a lot harder.

5

Pls note: I have fixed an important typo in the code above.

6

Thanks.

There may be a better way!

INSERT INTO x (a,b) VALUES ('one', 'two') RETURNING id;

This 'Returning id', returns the key value that is auto-incremented and was used to write that row!

I'm now researching how to get that 'id' to pass back to the Windform's c#.

Seems to be no downside.

7

That sounds like an Oracle feature. Microsoft SQL Server does not support the RETURNING keyword.

8

Well it's crude but it works..

SELECT id_key FROM a ORDER BY id_key DESC LIMIT 1

And that gives the last key used!

9

In an environment where multiple clients are inserting into the table, what you get may not correspond to the id for which you inserted the row. Did you run into any issues using the scope_identity function that @Kristen suggested?

INSERT INTO x (a,b) VALUES ('one', 'two') ;
SELECT SCOPE_IDENTITY() As Id;
10

The trouble is having to pass it back to C#.

I will try it tomorrow and see if the value can be passed back. If so, yes it's the superior method!

Thanks!

11

If you can get the recordset from

SELECT id_key FROM a ORDER BY id_key DESC LIMIT 1

easily enough then you should be able to get

SELECT SCOPE_IDENTITY() As Id

as easily? The only issue is that the SELECT needs to be in the same session as the INSERT, hence why I put the two together in the same statement, rather than doing two separate statements, but so long as you re-use the same connection to the database, for the second statement, I reckon you should be OK. I would want to check that a second user's INSERT didn't cause me to get a their ID back - so I'd want to do something like

INSERT ...
WAIT 10 seconds
SELECT SCOPE_IDENTITY() As Id;

and in the 10 second interval I would insert one/many rows on a different connection to then check whether the ID I got back was mine ... or from the second connection :slight_smile:

I note that "LIMIT 1" is not MS SQL, so you are probably on a different flavour of SQL, and I think that SCOPE_IDENTITY() may be specific to MS SQL, but there will be a similar function in other brands of SQL.