Solved

SQL -- identidy ID

Posted on 2014-03-17
19
201 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
  • 6
  • 5
  • 4
  • +2
19 Comments
 
LVL 29

Accepted Solution

by:
Rich Weissler earned 500 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
 

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
 
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 29

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
 

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 29

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
 

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 69

Expert Comment

by:ScottPletcher
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Expert Comment

by:ScottPletcher
ID: 39937458
Back thru 2005 (not avail. in SQL 2000).
0
 

Author Comment

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

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 69

Expert Comment

by:ScottPletcher
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 29

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 69

Expert Comment

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

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now