|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Wednesday, March 10, 2010 6:47 PM
Points: 26,
Visits: 82
|
|
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
*/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 24, 2010 3:44 PM
Points: 3,
Visits: 29
|
|
| If you are inserting the data as a series of discrete steps why don't you just read the scope_identity() after each insert?
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 10, 2010 6:47 PM
Points: 26,
Visits: 82
|
|
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.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 24, 2010 3:44 PM
Points: 3,
Visits: 29
|
|
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.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 9,104,
Visits: 8,528
|
|
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
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 10, 2010 6:47 PM
Points: 26,
Visits: 82
|
|
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
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 9,104,
Visits: 8,528
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 10, 2010 6:47 PM
Points: 26,
Visits: 82
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 24, 2010 3:44 PM
Points: 3,
Visits: 29
|
|
| 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.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 9,104,
Visits: 8,528
|
|
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.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
