SQL -- identidy ID

Hi,

is it possible to insert a record with a unique ID into tableA and then at the same time, use that unique ID to insert another record in tableB?
This is on SQL Server 2005
I can be more specific if need be, but I think this pretty much says it.

Thanks!
Nacht
LVL 1
nachtmskAsked:
Who is Participating?
 
Rich WeisslerConnect With a Mentor Professional Troublemaker^h^h^h^h^hshooterCommented:
Yes, you can use the SCOPE_IDENTITY() function.  (Or, if you don't have concurrency issues, the @@IDENTITY variable.)
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
nachtmskAuthor Commented:
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Carl TawnSystems and Integration DeveloperCommented:
It probably should. I don't know enough about Perl to be able to offer any concrete guidance.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
> 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.
0
 
nachtmskAuthor Commented:
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).
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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...)
0
 
nachtmskAuthor Commented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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.
0
 
nachtmskAuthor Commented:
Scott -- but that will only work in SQL 2012?
0
 
Scott PletcherSenior DBACommented:
Back thru 2005 (not avail. in SQL 2000).
0
 
nachtmskAuthor Commented:
Scott,
Thanks! Can you show me some example code implementing this?
N
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.)
0
 
Scott PletcherSenior DBACommented:
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 ?
0
 
Anthony PerkinsCommented:
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.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.
0
 
Anthony PerkinsCommented:
Thanks.  I stand corrected and I appreciate the reality check.
0
 
Scott PletcherSenior DBACommented:
Sorry I wasn't more helpful, it sure didn't seem that way.  Good luck on future qs!
0
 
nachtmskAuthor Commented:
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! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.