SQL to Count of Rows Read Inserted Updated Deleted

RJ 166 Reputation points
2024-09-16T17:52:38.2766667+00:00

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?

Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
0 comments No comments
{count} votes

Accepted answer
  1. Amira Bedhiafi 24,711 Reputation points
    2024-09-16T18:21:24.6966667+00:00

    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
    
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.