April 17, 2008 at 3:13 am
HI,
I want primary key of all Rows affected in UPDATE Statement.
i.e.
UPDATE M_Product set PRDm_Qty=5.5 where PRDm_Level='A'
it show me message like
(104 row(s) affected) in a result panle.
but i want to get primary key of all 104 record affected by UPDATE statement.
How can i get this?
Thanks in advance..
April 17, 2008 at 3:23 am
[font="Verdana"]Select {PK Col} From M_Product Where PRDm_Level='A'
[/font]
MH-09-AM-8694
April 17, 2008 at 3:38 am
Thanks Mahesh Bote ,
My problem gets Solve, now i need same primary key for INSERT statement. means i want Primerry key value of last inserted statement,
If only one row was insert using INSERT statement then i get it using @@identity or scope_identity().
but i m insert value using XML. so, may be more than one row gets insert using Single statement. for this if i use @@identity or scope_identity() , it give me last inserted row's primary key but i want primary key of all row inserted using last insert statement.
Thanks,
April 17, 2008 at 3:55 am
Assuming by 'primary key', you mean an identity column (they're not necessarily the same thing), you can use the technique in this example...
declare @Sample table (id int identity(1, 1) primary key, a int)
insert @Sample
select 7
union all select 3
union all select 4
insert @Sample
select 9
union all select 1
select id from @Sample where id between scope_identity() - @@rowcount + 1 and scope_identity()
/* Results
id
-----------
4
5
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 17, 2008 at 5:11 am
Ripal Patel (4/17/2008)
Thanks Mahesh Bote ,My problem gets Solve, now i need same primary key for INSERT statement. means i want Primerry key value of last inserted statement,
If only one row was insert using INSERT statement then i get it using @@identity or scope_identity().
but i m insert value using XML. so, may be more than one row gets insert using Single statement. for this if i use @@identity or scope_identity() , it give me last inserted row's primary key but i want primary key of all row inserted using last insert statement.
Thanks,
Ripal, can you explain the stuff in details with some code you have written? You need primary keys of all rows inserted using last statement, then again my previous post is useful. You will have all the PKs.
Mahesh
MH-09-AM-8694
April 17, 2008 at 5:53 am
Look at the OUTPUT clause in books online.
It will allow you to output the affected records into another statement.
April 17, 2008 at 6:14 am
Yes, please do what Micheal said. Use the OUTPUT statement to gather that information. Don't try to write logic to go back & capture it after the fact. When you get into a multi-user system with fairly active transactions, you'll get inconsistent results, slow downs & deadlocks.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 17, 2008 at 6:41 am
Well, I've learned something here. Thanks guys 🙂
I've implemented this in my example to demonstrate it...
declare @Sample table (id int identity(1, 1) primary key, a int)
insert @Sample
select 7
union all select 3
union all select 4
declare @inserted table (id int)
insert @Sample
output inserted.id into @inserted
select 9
union all select 1
select * from @inserted
/* Results
id
-----------
4
5
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply