Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patelb
Starting Member
16 Posts |
 Posted - 2008-07-29 : 19:46:31
|
| I am writing an application that has the need to compare two sql tables for differences. The first table is the master table with all of the records. Then periodically I will get updates for those records in the form of a CSV file. So I load the CSV file into a second table. Now I need to go through the second table record by record and compare them agaist each of the records in the first table, checking for things like updates of records, removal of records, addition of records. What would be the easiest way to do this? BTW I am using C#.Net. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-29 : 19:55:03
|
| Why don't you just get rid of all of the rows and then reload from your master table? Or just use replication to keep it up to date with the master table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
patelb
Starting Member
16 Posts |
Posted - 2008-07-29 : 19:59:47
|
| Actually im updating the master table according to the CSV file, ie if there is a record in the master table but its not present in the CSV file I would eventually want to delete it from the master table. If one record is updated in the CSV file, I would want to update that same record in the master table. So I have to compare all of the records in the CSV file, to all the records in the master table. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-29 : 20:06:09
|
| But if the CSV file is the owner of the data, then why not just reload the whole master table with whatever the file has? DELETE FROM MasterTable --or truncate if no foreign keysBULK INSERT...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
patelb
Starting Member
16 Posts |
Posted - 2008-07-29 : 20:12:19
|
| Because we need the functionality of accepting the changes or rejecting them. Ultimately I need to break things down into categories:1. records present in the CSV that are not in the master table(records to be added to master table)2. records present in the master table but not in the CSV(records to be deleted from master table)3. records that are present in both, but differ(records that need to be updated with data from CSV)4. records that are present in both but are exactly the same(do nothing)So I need to be able to seperate the records that fall into categories 1-3 so I can deal with them accordingly. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-29 : 20:17:08
|
| Import the data from the csv file into a staging table. You can do this with bulk insert/bcp/dts/ssis/import export wizard. Bulk insert would be easy if the file is accessible from the database server's perspective.Then:1. SELECT * FROM StagingTable s WHERE NOT EXISTS (SELECT * FROM MasterTable m WHERE m.PkColumn = s.PkColumn)2. Reverse the above query3. It's a bit tricky and I'm outta time today. My solution would be either EXISTS or OUTER JOIN.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-30 : 21:25:15
|
| or use tablediff utility:http://msdn.microsoft.com/en-us/library/ms162843.aspx |
|
|
|
|
|
|
Comparing Tables?