Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL -- identidy ID

Posted on 2014-03-17
19
Medium Priority
?
222 Views
Last Modified: 2014-03-21
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
0
Comment
Question by:nachtmsk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
19 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 2000 total points
ID: 39935586
Yes, you can use the SCOPE_IDENTITY() function.  (Or, if you don't have concurrency issues, the @@IDENTITY variable.)
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39936445
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
 
LVL 1

Author Comment

by:nachtmsk
ID: 39936605
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39936614
It probably should. I don't know enough about Perl to be able to offer any concrete guidance.
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 39936638
> 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
 
LVL 1

Author Comment

by:nachtmsk
ID: 39936866
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
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 39936904
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
 
LVL 1

Author Comment

by:nachtmsk
ID: 39936909
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39937299
>> 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
 
LVL 1

Author Comment

by:nachtmsk
ID: 39937353
Scott -- but that will only work in SQL 2012?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39937458
Back thru 2005 (not avail. in SQL 2000).
0
 
LVL 1

Author Comment

by:nachtmsk
ID: 39937495
Scott,
Thanks! Can you show me some example code implementing this?
N
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 39937532
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39937602
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938577
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
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 39938601
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938641
Thanks.  I stand corrected and I appreciate the reality check.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39945267
Sorry I wasn't more helpful, it sure didn't seem that way.  Good luck on future qs!
0
 
LVL 1

Author Comment

by:nachtmsk
ID: 39945273
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question