0

I need to insert one data into another table..after this step I need to fetch that inserted data only from that table...this whole I have to do using single stored procedure..using the concept of transactions in sql.

like I have one table abc that having content

id name
1  ashu

now I have inserted new data in it

id name
2  ashish

I don't know the data which I have inserted...I'm doing this by stored procedure...now I need those data which are inserted by this.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Ashu Rawat
  • 21
  • 8
  • The question title says sql server but you tagged this with plsql, what database are you using? – Taryn Apr 11 '13 at 11:50
  • Can you show the stored procedure you have written? – Kevin Brydon Apr 11 '13 at 11:51
  • my stored procedure is inserting more than one record at a time..and i want all those records only from that table...for that what i have to do....for single record fetching i can use @@identity property...but for multiple records what to do...help me out guys...please – Ashu Rawat Apr 11 '13 at 12:40
  • possible duplicate of [How to insert multiple records and get the identity value?](http://stackoverflow.com/questions/95988/how-to-insert-multiple-records-and-get-the-identity-value) Please make sure you look at the answer with the most votes in that question, not the accepted answer. You can use the [`OUTPUT` clause](http://msdn.microsoft.com/en-us/library/ms177564.aspx). – Pondlife Apr 11 '13 at 19:25

2 Answers2

0

You can use @@IDENTITY or SCOPE_IDENTITY() in SQL SERVER to return the last inserted identity, so at the end of your Stored Procedure do something like this:

SELECT * FROM YOUR_TABLE WHERE ID = @@IDENTITY

This will return the last row that was inserted.

See MSDN for information on @@IDENTITY AND SCOPE_IDENTITY()

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • @tanner.....your answer is correct in the case when i am inserting only one row..that will be fetched by @@identity property...if my stored procedure is inserting more than one record at a time..and i want all those records only from that table...for that what i have to do.... – Ashu Rawat Apr 11 '13 at 12:39
  • For multiple inserts, you could create a comma separated string to concatenate all of the identities as they are inserted "1,2,3,4" then use an `IN` clause on the `WHERE` clause so it would be WHERE ID IN(1,2,3,4). – Tanner Apr 11 '13 at 12:59
  • @tannan....but where i am inserting there i am already having uniqueids..so i can not hardcode...i do't know which id will generate for inserted records..without knowing id i want those 50 or 100 rows.. – Ashu Rawat Apr 11 '13 at 13:17
0

I got the solution for this. I am using:

Output inserted.SNO into @temp

From this command all the inserted rows will come in this temp table. So I am able to proceed to next steps.

Toon Krijthe
  • 52,876
  • 38
  • 145
  • 202
Ashu Rawat
  • 21
  • 8