Share This!

Friday, May 5, 2017

How to Check for any Changes in Table Data.

I am having a problem where some sneak process is changing the wrong
data in my inventory table. I wanted to discover how and when this was
happening, so I created a copy of my inventory table (on my local
machine, like this:
Select * from [dbo].[Inventory] into [dbo].[InventoryBASELINE]
Now I can check for discrepancies, like this...
select * from [dbo].[InventoryBASELINE] IB where CHECKSUM(*) <>
isnull((select CHECKSUM(*) from [dbo].[Inventory] I where IB.ID=I.ID),0)
union all
select * from [dbo].[Inventory] I where CHECKSUM(*) <> isnull((select
CHECKSUM(*) from [dbo].[InventoryBASELINE] IB where IB.ID=I.ID),0)
order by ID

This will show me any rows that are different, missing, or added between
my baseline table and my current table.
If I only cared about a few columns, I could use CHECKSUM(Customer, Qty,
Style) any columns I care about in all 4 checksum functions.

No comments:

Post a Comment

Contact Us


Email *

Message *