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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
It appears Scope_Identity works within a transaction. Have you tried it?
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
UnifiedISCommented:
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

.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john MAuthor Commented:
good to know it can't be done
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.