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.
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
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
but it will now let me do that.
Any thoughts on this/
Try this after the SET:
IF @InsertCount = @SourceCount BEGIN Print 'Good' -- DELETE FROM SourceTable; END
Thanks that worked
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.
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?)
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"