Identity on a Insert Select

Question

Post reply

Identity on a Insert Select

Aggie D

SSC-Addicted

Points: 443
More actions
May 3, 2004 at 8:21 am

#163558

Is there a way to do a select insert and get the identity for each record that is inserted?
for example if I do a:
INSERT INTO @t (fieldB) SELECT afIEldhERE from @T1
If @t has a field called FieldA that is an autonumber, is there a way to update @t1 with these values?
Thanks,

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

while if you are using a SP then use

then wrap this in a transaction and run

BEGIN TRAN

INSERT INTO @t (fieldB) SELECT afIEldhERE from @T1

update @t1 set filed = (select @@identity)

COMMIT TRAN

Thanks

Prasad Bhogadi
www.inforaise.com

Aggie D SSC-Addicted Points: 443 More actions · Answer 2

I am a little confused by your answer. Would this use a cursor to loop through all of the values to insert and insert them one at a time? If yes, is there a way to do this without using a cursor?

Also, if I am reading the code correctly, then, This would stick the last records identity into all values for the @t1 table. Example if you inserted 3 records then the id field for @t1 would all read 3.

gljjr SSChampion Points: 10080 More actions · Answer 3

Ummm I think what you want would be something like this...

DECLARE @iMin int

, @iMax int

DECLARE @t TABLE

(

intID int identity(1,1)

, value nvarchar(255)

)

DECLARE @t2 TABLE

(

intID int

, value nvarchar(255)

)

INSERT INTO @t(value) VALUES ('first')

INSERT INTO @t(value) VALUES ('second')

-- Get the maxid for the later insert.

SELECT @iMin = MAX(intID)

FROM @t

INSERT INTO @t(value) VALUES ('third')

INSERT INTO @t(value) VALUES ('fourth')

INSERT INTO @t(value) VALUES ('fifth')

SET @iMax = scope_identity()

INSERT INTO @t2(intID, value)

SELECT intID

, value

FROM @t

WHERE intID BETWEEN @iMin AND @iMax

SELECT * FROM @t

SELECT * FROM @t2

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

amitkhan SSC Enthusiast Points: 155 More actions · Answer 4

I think the same could be done using trigger on insert on said table @t. Within the trigger, you may please use the following to return all the identity values:

select FieldA from Inserted

I hope this way you would be able to retrieve all identity values inserted during the last insert using a select statement.

Regards

Amit

Regards,
Amit Khan

Ontrack Systems Limited
276B Lake Gardens
Kolkata - 700045
India
Phone - 91-33-24178434,35
Fax - 91-33-24221274
Mobile - 91-33-9830105090

Aggie D SSC-Addicted Points: 443 More actions · Answer 5

thanks for the post. I guess it might help if I explained a little more, because maybe i am going about this all wrong. I have an vb.net object that contains dates, times, and 0toN participants. I am working on a disconnected model in which the user can create a new hearing object and not write it to the database until they save the entire record. (hearing object is one small part of the bigger record).

The participants records are keyed off of the hearing key, which I do not get until after I have inserted the record. I could use the post where i rematch all of the records after insert, but there might be identical hearings. (I work with crazy people). That is why I was looking for a @@identity or scope_identity() function that would work on sets. Am I just totally off base with this? If at all possible, I would like to not use a cursor.

Any help is greatly appreciated.

gljjr SSChampion Points: 10080 More actions · Answer 6

David,

I would probably still use the approach I posted. I would just use table variables or temp tables as an interim step before doing the actual insert into the main tables. I'm assuming of course that you are going to be using stored procedures to do the inserting of the data that are called from the VB.Net application.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Aggie D SSC-Addicted Points: 443 More actions · Answer 7

yes, the system is using stored procedures. I am using 2 varchar(8000) to pass in xml strings produced by a dataset. I will try the temp table and see what I can get to work. BTW Gary, is the URL in your "AS IS" disclaimer needing an "o" in "copyright.htm"?

Thanks for the help.

gljjr SSChampion Points: 10080 More actions · Answer 8

Actually I'm removing the URL. After re-reading it the page is more geared for me than you Oh well. As an MS Employee I've been asked to put the disclaimer on my posts. So I was trying to do the right thing. That page is way overkill for the small scripts I post on this board. Thanks for making me re-read it!

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Aggie D SSC-Addicted Points: 443 More actions · Answer 9

Aggie D

SSC-Addicted

Points: 443

May 5, 2004 at 8:57 am

#505319

no problem, glad I could help.