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, V1rtAfter 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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
By far, what Madhivanan suggested, is going to be the easiest way to compare 2 tables. <br />Use this stored procedure and pass the table names as parameters. Since you are using linked servers, it should work well for you.<br /><br /<a target="_blank" href=http://www.sql-server-performance.com/vg_database_comparison_sp.asp>http://www.sql-server-performance.com/vg_database_comparison_sp.asp</a><br /><br />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=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
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
]]>