How to compare counts

1

Hi,
I want to get a count of the number of rows in a table before an insert into a history table and if see if the number of rows inserted was = to the number of rows in the count I had of the table that I am inserting from. How can I go about doing this.
AND then check if this is what happened. So, if the source table had 25 rows then the history table should have 25 new records.
Thank you

2 
DECLARE @SourceCount INT = 0, @InsertCount INT = 0;
SELECT @SourceCount = COUNT(*) FROM SourceTable;
-- Do your insert
SET @InsertCount = @@rowcount;
SELECT @SourceCount, @InsertCount;

Let me know if you have questions

3

This is great. the only thing that I would like to do from the is compare the two now. Because, if the number of source rows are = to the number that was inserted, then I want to truncate the source table. I tried doing something like
IF Sourcecount = insertedcount
print 'good'
but it will now let me do that.
Any thoughts on this/
Thank you

4

Try this after the SET:

IF @InsertCount = @SourceCount 
BEGIN
    Print 'Good'
--    DELETE FROM SourceTable; 
END
5

Thanks that worked
Thank you

6

You don't really need to count all the rows in the history table, which presumably could be quite large and will get larger over time.

Much more efficient and accurate would be to add an OUTPUT clause to the history INSERT statement, and verify that the number of rows it returns is the same as the number of rows in the source table.

7

is there a window-of-opportunity between you doing the INSERT and checking the COUNT for "another new row" to have been added (such that your COUNT won't match?)

How about

DELETE S
FROM MySourceTable AS S
WHERE EXISTS
(
    SELECT *
    FROM MyHistoryTable AS H
    WHERE H.SomeID = S.SomeID
)

so that you only delete rows that are in the History table and any rows in Source that are not (yet) in History will be Copied & Deleted "next time"