• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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
0
john M
Asked:
john M
  • 2
1 Solution
 
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
 
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

.
0
 
john MAuthor Commented:
good to know it can't be done
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now