SQL -- identidy ID

As mentioned, you can retrieve the ID of a newly added row with SCOPE_IDENTITY() to insert into the other table. But if you require both inserts to succeed/fail as a single unit then you'll need to wrap it in a transaction.

Hi,

Thanks!
Can this be done with up to 5 records simultaneously?
Also, I'm doing this through Perl connecting via DBI/ODBC on a win2008 machine. Maybe this should be a separate question?

Nacht

It probably should. I don't know enough about Perl to be able to offer any concrete guidance.

> Can this be done with up to 5 records simultaneously?
Assumption: One record created in Table A, and you want to capture the Identity value assigned to that record, and write five records into Table B with that identity.  -- Sure!  No problem.

If you want to create five records in Table A simultaneous, and capture those five identity values to write records into Table B with five different identity values... not so easy.  You can use an alternate method to create the identity values in that case... but you won't be taking advantage of the identies in SQL 2005.

And sorry, I don't know Perl... but assuming you're just issuing TSQL wrapped in Perl, I can't imagine the Perl would introduce an issue.

Hi Razmus,

Thanks. It's the harder of the two, five records from Table A to 5 records in Table B with Different Identity values.
I wondering if I created 5 different stored Procs and called each one of them independently, but at the same time.

Is this even a good idea? I have a client who is mildly insisting this be possible (the details of the app are probably not necessary here). I strongly suggested a two step process. First create the user (and get the ID), then create a record in a different table using that users ID (it's a payroll table to keep track of employee hours).

If you do decide to do it in one procedure, you might want to consider deriving the numbers from something other than the Identity.  Itzik Ben-Gan has written some of the best articles I've seen on the subject, and goes into some detail in his T-SQL Programming book.

SQL 2012 introduces a Sequence Number, and the guidance for it's use includes the suggestion that it be used when, "An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once."  (Really -- I support page even specified five records at once... I didn't make that up.  :-) )  I know you specified SQL 2005... which is out of support, unfortunately.  If you can possibly use the newer version... well, there is an object that might support your need better.  (Apparently Sequences have been in Oracle for longer...)

Ok Raz, thanks very much!
That was very helpful. It's an intriguing thing to do and using 2012 seems the best way to go. I highly doubt my client will want to upgrade first to 2012, but I might install 2012 here in my lab just to play around with the idea.

Nacht

>> five records from Table A to 5 records in Table B with Different Identity values. <<

No problem.  You can use the "OUTPUT" clause of the input to get all the identity values that were INSERTed.  If you need assistance with that, just let me know.

Scott -- but that will only work in SQL 2012?

Back thru 2005 (not avail. in SQL 2000).

Scott,
Thanks! Can you show me some example code implementing this?
N

This blog has examples of using OUTPUT to capture the Identity values.  (I'm embarrassed that I hadn't thought of that.  You'll still want to wrap everything in a transaction, as Carl mentioned, and don't assume the IDENTITY values will be sequencial on a busy system.)

Something like below.

The specifics depend on whether or not you need just the table "a" identity numbers or if you need to get other matching/joined data for each identity individually.


DECLARE @inserted_rows TABLE (
    id int,
    key_column int --if needed
    --,...other column(s), as needed
    )

INSERT INTO dbo.tableA ( ...column_list... )
OUTPUT INSERTED.ID, INSERTED.key_column /*,...*/ INTO @inserted_rows
--rest of normal INSERT statement
SELECT ... | VALUES(...)

INSERT INTO dbo.tableB
SELECT ir.id, ...
FROM @inserted_rows ir
--INNER JOIN ?

don't assume the IDENTITY values will be sequencial on a busy system.
Supposing you insert a million rows into a table with a single INSERT statement and simultaneous do the same in a different session, could you not assume that each INSERT statement range would be sequential?  In other words assuming the table was empty, wouldn't the one insert have the 1-1,000,000 and the other INSERT statement 1,000,001-2,000,000.

I honestly don't know the answer, I always assumed the first INSERT statement would "lock" the IDENTITY value down until it completed.  I guess it would be pretty easy to test.

You could possibly test it, and the tests could come out that the numbers are sequencial... and with a future patch, the behaviour could change.  
From MSDN:

The identity property on a column does not guarantee the following:
[...]
--Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.
[...]

It might turn out that every test has them come out sequential, but I still wouldn't advise building anything using any logic that counted on that behaviour... well, unless you were willing to put additional locks on the table during the transaction.

Thanks.  I stand corrected and I appreciate the reality check.

Sorry I wasn't more helpful, it sure didn't seem that way.  Good luck on future qs!

Scott -- actually you were very helpful.
In the end though I only needed the @@IDENTITY solution. That's why I gave credit to that one. I probably should have given points to multiple answers, yours included. Sorry!  I'll get you next time -- there will always be a next time! :)