January 8, 2009 at 1:18 pm
for example, if i insert a batch of say five rows in table IdentTest (UserID int identity(1,1), UserName varchar(10)), i'd like to collect all the UserIDs just created. I know if can collect the last one by selecting the value of Scope_Identity(). But the rest i just can't seem to get to!
does anyone know a way to get these values cleanly? Anyone know of a trick to get them?
Thanks very much for anyone who'd like to help. Below is a script to help you and to illustrate further my problem:
/* Create the tables */
Create table UsersTest (UserName varchar(10))
create table IdentTest (UserID int identity(1,1), UserName varchar(10))
/* Data insert 1 */
insert into UsersTest (UserName) values ('User1')
insert into UsersTest (UserName) values ('User2')
insert into UsersTest (UserName) values ('User3')
insert into UsersTest (UserName) values ('User4')
insert into UsersTest (UserName) values ('User5')
/* data insert 2 */
insert into IdentTest (UserName)
select UserName from UsersTest
/* here is where i want all the identities but can only get one */
select Scope_Identity()
/*
drop table UsersTest
drop table IdentTest
*/
January 8, 2009 at 1:46 pm
If you are inserting the data as a series of discrete steps why don't you just read the scope_identity() after each insert?
January 8, 2009 at 1:50 pm
the discrete steps were just to load the data for the example. The actual insert is a bulk insert in the form of
Insert into TableA(ID)
select ID from TableB
so, lots of IDs going in at once. Thanks.
January 8, 2009 at 1:57 pm
I guess that practically speaking you would have to do something along the lines of:
1. Create a transaction and lock the destination table.
2. Check that the current identity value is no less than the maximum value of the identity column in the table. You can use dbcc checkident (among other things) for this. Read the value of the next identity that will be inserted. This will usually be whatever dbcc checkident returns plus 1. Call this @StartID.
3. Bulk insert your data and count rows. Call the rowcount @rc
4. Since the table is locked the new identity values should be between the value @StartID and @StartID + @rc - 1.
5. Commit the transaction.
January 8, 2009 at 2:29 pm
A much better way is to use the OUTPUT clause,like this:
--====== Make our test table to insert to
Create Table InsertIDTst(
ID int identity primary key
, ColName nvarchar(255)
, object_id int);
GO
--====== Make a table variable to hold the new ID's
Declare @IDList Table(ID int);
--====== Insert a bunch of rows,
--and save the new IDs at the same time
INSERT into InsertIDTst(ColName, object_id)
Output INSERTED.ID Into @IDList(ID)
Select name, object_id
From sys.columns
--====== Show that we have the new IDs
SELECT * from @IDList
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 4:12 pm
Fantastic. Thanks for everyone who took the time to think about, and reply to, my question. Thanks especially to RBarryYoung who's solution elegantly solves the whole problem. i had no idea about the Output thingy. it will come in very handy.
-drew
January 8, 2009 at 4:22 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 11, 2009 at 4:59 pm
is there a way to mark a topic as finished, or mark that the question has been answered correctly/helpfully? seems like this would be quite a useful feature.
-d
January 11, 2009 at 5:28 pm
The way that these things usually work is that the post drifts downwards in the list as people fail to reply. If someone replies usually the post goes to the top of the list. Of course that might not be the case here :P.
January 11, 2009 at 5:41 pm
bagofbirds (1/11/2009)
is there a way to mark a topic as finished, or mark that the question has been answered correctly/helpfully? seems like this would be quite a useful feature.-d
Once you tell us that you have what you want, we usually stop replying. Of course sometime's we end up talking about something else... 🙂
You can also unsubscribe from the topic.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 12:23 am
there is another way which you can use..
1. Take the MAX(userID) before batch insert. this will give you the last UserID before batch insert
2. Perform batch insert.
3. Retrive those records which are greater than MAX(userID) (step 1).
this will give you the identity for all newly inserted UserID after batch insert.
Abhijit - http://abhijitmore.wordpress.com
January 13, 2009 at 6:47 am
Abhijit More (1/13/2009)
there is another way which you can use..1. Take the MAX(userID) before batch insert. this will give you the last UserID before batch insert
2. Perform batch insert.
3. Retrive those records which are greater than MAX(userID) (step 1).
this will give you the identity for all newly inserted UserID after batch insert.
Which will not work if someone else inserts records between step 1 and step 3.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 14, 2009 at 6:36 am
RBarryYoung said
A much better way is to use the OUTPUT clause,like this:
...
INSERT into InsertIDTst(ColName, object_id)
Output INSERTED.ID Into @IDList(ID)
Select name, object_id
From sys.columns
...
When I first found out about the OUTPUT clause, I was overjoyed. The main problem that I have been trying to solve, I thought it would help with. But it appears that it is not sufficient by itself.
I am wanting to use it to capture both my source table's ID and the target table's ID. The target's ID is not a problem. But since I am not including the source's ID (there is no place to put it in the target table), it appears that I am just out of luck. I was hoping to use this to easily create a cross-reference table so I would know where my rows came from. The only way I can see to do this is to modify the target table to add a column for this. Does anyone have any other ideas?
My apologizes for hijacking this thread
Scott
January 14, 2009 at 6:57 am
Yep, it's true, the OUTPUT clause cannot pull from your input sources, only the data that you are already outputting.
When I've been in your situation, I have always added a SourceID column to the table to get my correlation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 14, 2009 at 7:02 am
Hmm, isn't UserName sufficient to correlate it back to the source? You could include that in the OUTPUT clause.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply