SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get number of rows updated/Inserted/deleted by boclk of t-sql code


Get number of rows updated/Inserted/deleted by boclk of t-sql code

Author
Message
madhu-686862
madhu-686862
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 1688
Hi,

In Oracle, we use SET SERVEROUTPUT ON to display the row count and SQL%ROWCOUNT to get actual no.of rows updated/Inserted/deleted by boclk of pl-sql code

For example, I run the below pl-sql code from sqlplus as below:

save the pl-sql code to a .sql file, update.sql

from sqlplus, connect to oracle database and execute the file update.sql

If the row count is correct, then issue COMMIT otherwise issue ROLLBACK.


SET SERVEROUTPUT ON;

DECLARE
v_count NUMBER := 0;
BEGIN
UPDATE staff
SET function_role =
(SELECT function_role
FROM function_role
WHERE functional_role_name = 'CLR')
WHERE UPPER (first_name) = UPPER ('scott')
AND UPPER (last_name) = UPPER ('Harrison');

v_count := v_count + SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE ('Updated ' || v_count || ' rows in STAFF Table');
END;
/

How to achive the same in SQL Server?

Thanks
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4046 Visits: 5843
I think @@ROWCOUNT is what you're after.

http://technet.microsoft.com/en-us/library/ms187316(v=sql.105).aspx

As in, SET v_count = @@ROWCOUNT.

Cheers
madhu-686862
madhu-686862
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 1688
below is the pl-sql code.

It, displays the number of total records inserted/updated/deleted.
If the count matches, will issue COMMIT otherwise rollback.


SET SERVEROUTPUT ON;

DECLARE

v_update number := 0;
v_delete number := 0;
v_insert number := 0;

Begin


delete from bill_detail where bill_detail in (9878506)
and policy = 4236807;
v_delete := v_delete + SQL%ROWCOUNT;


update prem set written_prem = 50,
commission_amt = 7.50,
audit_id = USER,
last_modified = SYSDATE
where prem in (85272831)
and policy = 3567140;
v_update := v_update + SQL%ROWCOUNT;

update bill_detail set gross_amt = 50,
commission_amt = 7.50,
net_amt = 42.5
where bill_detail = 9881358 and policy = 3567140;
v_update := v_update + SQL%ROWCOUNT;

update installment set remaining_prem = 50,
installment_status = 'Future',
-- original_prem = 501,
total_due = 50,
paid_date = NULL,
bill_date = NULL
where installment = 21820355
and policy = 3567140;
v_update := v_update + SQL%ROWCOUNT;



DBMS_OUTPUT.PUT_LINE('ROWS UPDATED ' || v_update);
DBMS_OUTPUT.PUT_LINE('ROWS DELETED ' || v_delete);
DBMS_OUTPUT.PUT_LINE('ROWS INSERTED ' || v_insert);

END;
/

What is the equivalent code in t-sql to achieve the same as above?
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40030 Visits: 38566
You were told to use the @@ROWCOUNT function. Here is how you would use it:


delete from bill_detail where bill_detail in (9878506)
and policy = 4236807;
SET @v_delete = @@ROWCOUNT;


update prem set written_prem = 50,
commission_amt = 7.50,
audit_id = USER,
last_modified = SYSDATE
where prem in (85272831)
and policy = 3567140;
SET @v_update = @@ROWCOUNT;

update bill_detail set gross_amt = 50,
commission_amt = 7.50,
net_amt = 42.5
where bill_detail = 9881358 and policy = 3567140;
SET @v_update = @v_update + @@ROWCOUNT;

update installment set remaining_prem = 50,
installment_status = 'Future',
-- original_prem = 501,
total_due = 50,
paid_date = NULL,
bill_date = NULL
where installment = 21820355
and policy = 3567140;
SET @v_update = @v_update + @@ROWCOUNT;





Not saying the code above will work in SQL Server as the only parts I modified were where @@ROWCOUNT is used.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26384 Visits: 17557
Something like this?


declare @Delete int, @Update int, @Insert int

delete from bill_detail where bill_detail in (9878506)
and policy = 4236807;

select @Delete = @@ROWCOUNT

update prem set written_prem = 50,
commission_amt = 7.50,
audit_id = USER,
last_modified = SYSDATE
where prem in (85272831)
and policy = 3567140;

select @Update = @@ROWCOUNT

update bill_detail set gross_amt = 50,
commission_amt = 7.50,
net_amt = 42.5
where bill_detail = 9881358 and policy = 3567140;

select @Update = @Update + @@ROWCOUNT

update installment set remaining_prem = 50,
installment_status = 'Future',
-- original_prem = 501,
total_due = 50,
paid_date = NULL,
bill_date = NULL
where installment = 21820355
and policy = 3567140;

select @Update = @Update + @@ROWCOUNT

print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
madhu-686862
madhu-686862
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 1688
Sean,

I executed the below code as you suggested and I'm getting the below results in SSMS


declare @Delete int, @Update int, @Insert int


DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'CopyRequested'
select @Delete = @@ROWCOUNT

DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'AdditionalIntrestType'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','entity','Entity')
select @insert = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','individual','Individual')
select @insert = @@ROWCOUNT

UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 'DirectBill'
,[VALUE_MTD] = 'Direct Bill'
WHERE [ETYPE_EWT] = 'TypesOfBill' and [KEY_MTD] = 'Direct'
select @update = @@ROWCOUNT

UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 10
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Completed'
select @Update = @@ROWCOUNT

DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Declined'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('ApplicationStatus'
,11
,'Application Declined')
select @Insert = @@ROWCOUNT

print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'



(0 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

1 Row(s) Updated
1 Row(s) Deleted
1 Row(s) Inserted

But looking at the results, row(s) affected and the total count, it's not matching.

And I want to rollback it and correct the t-sql. Please advise.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26384 Visits: 17557
Did you put this inside a transaction? If so, just "ROLLBACK TRANSACTION"

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40030 Visits: 38566
madhu-686862 (5/8/2012)
Sean,

I executed the below code as you suggested and I'm getting the below results in SSMS


declare @Delete int, @Update int, @Insert int


DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'CopyRequested'
select @Delete = @@ROWCOUNT

DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'AdditionalIntrestType'
select @Delete = @@ROWCOUNT

INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','entity','Entity')
select @insert = @@ROWCOUNT

INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','individual','Individual')
select @insert = @@ROWCOUNT

UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 'DirectBill'
,[VALUE_MTD] = 'Direct Bill'
WHERE [ETYPE_EWT] = 'TypesOfBill' and [KEY_MTD] = 'Direct'
select @update = @@ROWCOUNT

UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 10
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Completed'
select @Update = @@ROWCOUNT

DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Declined'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('ApplicationStatus'
,11
,'Application Declined')
select @Insert = @@ROWCOUNT

print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'



(0 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

1 Row(s) Updated
1 Row(s) Deleted
1 Row(s) Inserted

But looking at the results, row(s) affected and the total count, it's not matching.

And I want to rollback it and correct the t-sql. Please advise.




declare @Delete int, @Update int, @Insert int


DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'CopyRequested'
select @Delete = @@ROWCOUNT <<<< Sets @Delete to number of rows deleted

DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'AdditionalIntrestType'
select @Delete = @@ROWCOUNT <<<<< Sets @Delete to number of rows deleted, previous value lost

INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','entity','Entity')
select @insert = @@ROWCOUNT <<<< Sets @insert to number of rows inserted

INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','individual','Individual')
select @insert = @@ROWCOUNT <<<< Sets @insert to number of rows inserted, previous value lost

UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 'DirectBill'
,[VALUE_MTD] = 'Direct Bill'
WHERE [ETYPE_EWT] = 'TypesOfBill' and [KEY_MTD] = 'Direct'
select @update = @@ROWCOUNT <<<< Sets @updateto number of rows updated

UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 10
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Completed'
select @Update = @@ROWCOUNT <<<< Sets @updateto number of rows updated, previous value lost

DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Declined'
select @Delete = @@ROWCOUNT <<<< Sets @Deleteto number of rows deleted, previous value lost
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('ApplicationStatus'
,11
,'Application Declined')
select @Insert = @@ROWCOUNT <<<< Sets @insert to number of rows inserted, previous value lost

print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'


Did you happen to look at some of what I put in my post above? You know, SET @update = @update + @@ROWCOUNT; (for example)

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search