Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get number of rows updated/Inserted/deleted by boclk of t-sql code Expand / Collapse
Author
Message
Posted Tuesday, May 8, 2012 10:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688

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
Post #1296608
Posted Tuesday, May 8, 2012 10:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 2,057, Visits: 3,578
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
Post #1296614
Posted Tuesday, May 8, 2012 12:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
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?

Post #1296663
Posted Tuesday, May 8, 2012 12:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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.



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)
Post #1296668
Posted Tuesday, May 8, 2012 12:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's 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)
Post #1296670
Posted Tuesday, May 8, 2012 12:36 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
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.
Post #1296679
Posted Tuesday, May 8, 2012 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's 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)
Post #1296683
Posted Tuesday, May 8, 2012 12:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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)



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)
Post #1296684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse