SQL to Count of Rows Read Inserted Updated Deleted

Question

Hi there,

Im looking to log count of row read, inserted, updated, delete while processing data within a SP.

Is there any best practice or sp template to catch these counts?

You could see inorder to catch the count, the data (millions of rows) is moved into temp table and then count is queried on temp table. Is this the only way? cant i capture count on the fly on insert into select * or select * into

Below is an example

My current SP layout is something like this.

create sp as processcustomer
Begin
-- customer temp table
  CREATE TABLE #Customer 
  (    [UniqueCustomerID] VARCHAR(200)  
  );  
--final table
INSERT INTO dim.customer  
(  col1,col2,col3
)  
 OUTPUT INSERTED.[UniqueCustomerID]  
INTO #Customer
SELECT col1, col2, col3 FROM [staging].[Customer]
--logging
SELECT @Rows = ISNULL(COUNT(1), 0) FROM #Customer; 
insert into logging table (rowsread)
select @rows
end

Q2: Sometimes joins happen in multiple temp tables which i feel is waste since the under lying tables have indexes but once it goes into temp table the effectiveness of index is there in query or a index is created on the temp tables.

Q3: Is there a way to catch counts on upsert merge and not?

Accepted Answer

Capturing row counts on the fly during an INSERT INTO SELECT or SELECT INTO operation can be done without temp tables by leveraging @@ROWCOUNT. This system function returns the number of rows affected by the last statement. Here's an example:


CREATE PROCEDURE ProcessCustomer

AS

BEGIN

    DECLARE @RowsInserted INT;

    -- Insert into the final table and capture row count

    INSERT INTO dim.customer (col1, col2, col3)

    SELECT col1, col2, col3 FROM [staging].[Customer];

    -- Capture row count directly after the INSERT

    SET @RowsInserted = @@ROWCOUNT;

    -- Log row count

    INSERT INTO logging (RowsInserted) VALUES (@RowsInserted);

END

While it's true that inserting data into temp tables can affect performance (as they lose the benefits of indexed source tables), indexing temp tables can improve the speed of subsequent operations if you're performing complex joins. However, if your original tables are already indexed well and you don't modify the data significantly, consider avoiding temp tables. You could rewrite queries to work directly with the original indexed tables or use table variables for smaller datasets to improve performance.

For MERGE operations, you can capture counts of inserted, updated, and deleted rows using the OUTPUT clause. Here’s an example that captures and logs the count of affected rows:

CREATE PROCEDURE MergeCustomers

AS

BEGIN

 DECLARE @RowsInserted INT = 0, @RowsUpdated INT = 0, @RowsDeleted INT = 0;

 MERGE INTO dim.customer AS Target

 USING staging.customer AS Source

 ON Target.CustomerID = Source.CustomerID

 WHEN MATCHED THEN

     UPDATE SET col1 = Source.col1, col2 = Source.col2

 WHEN NOT MATCHED BY TARGET THEN

     INSERT (col1, col2) VALUES (Source.col1, Source.col2)

 WHEN NOT MATCHED BY SOURCE THEN

     DELETE

 OUTPUT

     $action, INSERTED.CustomerID

 INTO @MergeOutputTable;

 -- Capture row counts

 SELECT 

     @RowsInserted = SUM(CASE WHEN action = 'INSERT' THEN 1 ELSE 0 END),

     @RowsUpdated = SUM(CASE WHEN action = 'UPDATE' THEN 1 ELSE 0 END),

     @RowsDeleted = SUM(CASE WHEN action = 'DELETE' THEN 1 ELSE 0 END)

 FROM @MergeOutputTable;

 -- Log row counts

 INSERT INTO logging (RowsInserted, RowsUpdated, RowsDeleted) 

 VALUES (@RowsInserted, @RowsUpdated, @RowsDeleted);

END

Share via

SQL to Count of Rows Read Inserted Updated Deleted

0 additional answers

Your answer