@@RowCount returns the number of row/s affected by last statment. Let's see how it is used in real time.
Let's say that we want to insert new records from dbo.source table to our dbo.destination table. if IDs do not exist in destination table then insert otherwise update the record. To perform this we will write stored procedure.
We want to insert the row counts for updated and inserted records in dbo.Audit table in Stored procedure too for audit process.
Let's say that we want to insert new records from dbo.source table to our dbo.destination table. if IDs do not exist in destination table then insert otherwise update the record. To perform this we will write stored procedure.
We want to insert the row counts for updated and inserted records in dbo.Audit table in Stored procedure too for audit process.
USE TestDB GO
--> Prepare dbo.Source Table
CREATE TABLE dbo.Source ( ID INT, Name VARCHAR(100) )
GO
--> Insert couple of records in dbo.Source Table
INSERT INTO dbo.Source
VALUES (1, 'Aamir'), (2, 'Raza')
GO
-->Create dbo.Destination Table.
CREATE TABLE dbo.Destination ( ID INT, Name VARCHAR(100) )
GO
--> Insert only one records in dbo.Destination table
INSERT INTO dbo.Destination
VALUES (1, 'Aamir Shahzad')
GO
--> Create dbo.Aduit table that will hold SourceTable,DestinationTable,updatedRowCnt,InsertRowCnt
CREATE TABLE dbo.Audit ( id INT IDENTITY(1, 1), SourceTableName VARCHAR(100), DestinationTableName VARCHAR(100), UpdatedRecords INT, InsertedRecords INT, LoadDate DATETIME DEFAULT GETDATE() ) GO
--> Create Stored Procedure for Insert/Update CREATE PROCEDURE Dbo.Updatedestination
AS BEGIN DECLARE @UpdatedRecords INT DECLARE @InsertedRecprds INT -->Update Exisiting Records UPDATE DST SET DST.Name = SRC.Name FROM dbo.Destination DST INNER JOIN DBO.Source SRC ON DST.ID = SRC.ID
--SET the values of @updatedRecords variable by using @@RowCount SET @UpdatedRecords=@@ROWCOUNT -->Insert New Records INSERT INTO dbo.Destination (ID, Name) SELECT ID, Name FROM dbo.Source S WHERE NOT EXISTS (SELECT 1 FROM dbo.Destination D WHERE S.ID = D.ID)
--> Set the value of @InsertedRecords variable by using @@ROWCOUNT SET @InsertedRecprds=@@ROWCOUNT --> Insert Row Counts into dbo.Audit Table INSERT INTO dbo.Audit (SourceTableName, DestinationTableName, InsertedRecords, UpdatedRecords) SELECT 'dbo.Source', 'dbo.Destination', @InsertedRecprds, @UpdatedRecords END -->Execute Stored Procedure couple of times
EXEC dbo.Updatedestination --> Check the Audit Table
SELECT * FROM dbo.Audit --Drop All object which created for this scenario --drop table dbo.Source --drop table dbo.Destination --drop table dbo.Audit --drop procedure dbo.Updatedestination
Fig 1: Records affected
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
ReplyDeletehttp://chennaitraining.in/sap-rem-training-in-chennai/
http://chennaitraining.in/sap-scm-training-in-chennai/
http://chennaitraining.in/sap-sd-training-in-chennai/
http://chennaitraining.in/sap-sm-training-in-chennai/
http://chennaitraining.in/sap-srm-training-in-chennai/
http://chennaitraining.in/sap-success-factor-training-in-chennai/
http://chennaitraining.in/sap-tr-training-in-chennai/
http://chennaitraining.in/sap-wf-training-in-chennai/