Post
Ask Question

Compare Tables and/or Data to Know What was Changed

Sometimes we need to compare tables and/or data to know what was changed.

Solution:

Script for create two table and insert value in both table:

  1. USE Learn  
  2. GO  
  3. CREATE TABLE [dbo].[product1](  
  4. [id] [nchar](10) NOT NULL,  
  5. [type] [nvarchar](10) NULL,  
  6. [cost] [nchar](10) NULL,  
  7. CONSTRAINT [PK_product1] PRIMARY KEY CLUSTERED   
  8. (  
  9. [id] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  11. ) ON [PRIMARY]  
  12. GO  
  13. insert into [dbo].[product1] values  
  14. ('001','Book1','40'),  
  15. ('002','Book1','80'),  
  16. ('003','Book13','120')  
  17. GO   
  18. CREATE TABLE [dbo].[product2](  
  19. [id] [nchar](10) NOT NULL,  
  20. [type] [nvarchar](20) NULL,  
  21. [cost] [nchar](10) NULL,  
  22. CONSTRAINT [PK_product2] PRIMARY KEY CLUSTERED   
  23. (  
  24. [id] ASC  
  25. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  26. ) ON [PRIMARY]  
  27. GO  
  28. insert into [dbo].[product2] values  
  29. ('001','Book1','40'),  
  30. ('002','Book12','80'),  
  31. ('003','Book13','120'),  
  32. ('004','Book14','160')  
  33.   
  34. select * from [product1]  
  35.   
  36. select * from [product2]  
Following Query show difference between table:

select * from [product2]

except

select * from [product1]