SQL Server Performance Forum – Threads Archive

reliable comparing of data

how are you guys going to validate that the data you copied from table A to table B assuming there are 50+ columns and hundreds of thousands of rows are identical? Thanks, V1rt

After you copied the data, try Select * from table1 t where not exists(select * from table2 where keycol=T.keycol) If you dont get any rows then they are identical Madhivanan Failing to plan is Planning to fail

BAM! Thanks! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Check for error messages. If the insert failed for any rows, then the whole insert has failed, unless you are copying one row at a time.

Ok, I have a new issue. I found out that I should be comparing this data to a remote sql instance, the source. I used sp_addlinkedserver and it failed. And the owner of the server doesn’t want to put my account in the admin group. That sp requires admin group. What are my other solutions to compare data from one server to another? Thanks guys!

True that SP are defined to have execute permissions default to members of the sysadmin and setupadmin fixed server roles. If not ask owner to put your login in setupadmin. Otherwise ask them to run the procedure of finding duplicates in order to get the results. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I got the linked server fixed. I just asked them to create a SQL account for me. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> However, I was thinking about Madhivanan query and I think, it’s going to work but will not address my question. Select * from table1 t where not exists(select * from table2 where keycol=T.keycol) assuming both tables have 1million rows and keycol fields exactly matches each other. The query above will return 0 because they matched. However, it’s not going to work if we have another field on that same table that’s been modified by someone. The query above will still return 0 because we are using keycol only as the key to match both tables. So going back to my question, how will I compare all fields? It’s like saying, table1 is a mirror of table2. Any help would be greatly appreciated. Thanks!

Seems like you have to compare all the columns. That will be slow, but the only way I know of to compare the data is to compare it <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

By far, what Madhivanan suggested, is going to be the easiest way to compare 2 tables. Use this stored procedure and pass the table names as parameters. Since you are using linked servers, it should work well for you. http://www.sql-server-performance.com/vg_database_comparison_sp.asp</a> There is a tool by red-gate called data compare, but I am not sure if you want to use a 3rd party app. The link below refers to a suggested way, and you can modify their SP to get the column names dynamically. Otherwise, you will have to fill out all the column names for at least one table, so I am not sure if that’s much help. You could always use sp_columns and cut and paste the results to a text editor <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054</a> You can try to use sp_table_validation and check the checksum values of both tables for differences. This is more of a "yes" or "no" approach, but won’t give you what those differences are.

I’ve created the procedure found athttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054
It’s currently running. So how does that script know that we want to compare this data aganst this data since it’s not using a key?

The key is the columns that you are passing Madhivanan Failing to plan is Planning to fail

]]>