tsgl -can you get the Scope_Identity for each record added in when Inserting mulitple records in a table

I have a stored procedure that is adding muliple records when it is runs.
What I want to do is build a work file with the Scope_Identity() 'key" of each record I add.
Well for one of the tranactions.
I do have 3 tranacations in this stored procedure

1) delete all records in a work table


2) insert all records
  So when this runs it usually adds 30/40 records a run. I want to build in another work table with the Scope_Identity() = key of record added.



3) add comment records
LVL 3
john MAsked:
Who is Participating?
 
UnifiedISConnect With a Mentor Commented:
You won't be able to get the identity for each record unless your insert only adds one record at a time.

In other words, if you are doing a INSERT INTO..SELECT FROM where the SELECT FROM will have many values, it won't provide the intended results

.
0
 
CodeCruiserCommented:
It appears Scope_Identity works within a transaction. Have you tried it?
0
 
john MAuthor Commented:
i am not sure how to get it.

i know you can  set it
 set keyfound = scope_idenity()

insert into filea
(ssn,
name)
select
ssn,
name
from
bfileb
how are you getting it?

then how do you provide to write a tempwork Table?

insert into tempfiles
(keyoflastrecord,
date)
select
scope_idenity()
date
from
filec
0
 
john MAuthor Commented:
good to know it can't be done
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.