Table comparison
-
I need to compare two tables in sql server. they are located on different databases. I need to compare the data. The tables have about 200 fields in them. IS there a way to compare this data and know which field does not match? Using a hashtotal, would say if two rows are different but does not which data is exactly different. And it gets tough with 200 fields in a table. Also the size of the table is big, about 100000 rows at minimum. Thank you so much.
-
I need to compare two tables in sql server. they are located on different databases. I need to compare the data. The tables have about 200 fields in them. IS there a way to compare this data and know which field does not match? Using a hashtotal, would say if two rows are different but does not which data is exactly different. And it gets tough with 200 fields in a table. Also the size of the table is big, about 100000 rows at minimum. Thank you so much.
I suggest you create a copy of the second table in the first server - otherwise your compare will be delayed by the network.
vanikanc wrote:
Using a hashtotal, would say if two rows are different but does not which data is exactly different. And it gets tough with 200 fields in a table.
200 fields? How about normalizing that table? You could write a small table-valued function that compares each field individually for a given record and that spits out only the columnnames where there's differences, by looping through all the columns (see
sys.columns). Another option might be Sql Compare[^] from Red Gate. It shows the difference in your data, but also on a row-by-row level. The changed columns are highlighted in a different color though, and there's a trial-version available.Bastard Programmer from Hell :suss:
-
I need to compare two tables in sql server. they are located on different databases. I need to compare the data. The tables have about 200 fields in them. IS there a way to compare this data and know which field does not match? Using a hashtotal, would say if two rows are different but does not which data is exactly different. And it gets tough with 200 fields in a table. Also the size of the table is big, about 100000 rows at minimum. Thank you so much.
How about using the Hashtotal or checksum concept and if the two rows are different, then write them out to 2 different text files (FileA & FileB). At the end of the process, you could use a tool like BeyondCompare to compare the data in the two files. Just a thought. Awesome tool to have in your bag of tricks ... http://www.scootersoftware.com/[^]