Unable to run diff on extracted tables

[SQLAdminCJ]

Hey all,

I am trying to troubleshoot an exception that the data diff program that I wrote has been giving me.Hey all,

I am trying to troubleshoot an exception that the data diff program that I wrote has been giving me on a client's server. Their database is very large, and the error is caused during the process of running the commands to sync a specific table. There is a slightly different version of the database on each of 2 servers.

Rather than bring all of both databases back to my dev environment, I decided to create a new database on each server, and copy only the specific table into the new database using select into.

Since there were 2 servers, this means I had two of these databases containing only this one table. I brought them back to the office and ran my utility, but the table differences are coming back null even though at the client site itself it shows many differences.

Does anyone have any idea of what could be going on? What am I not transferring when copying the table using select into that is necessary to diff the two tables? I would appreciate any advice that anyone can provide. Thanks.

[SQLAdminCJ]

Hey all,

I found out why the diff wasn't working when the tables was copied via this method; the primary key on the table is not preserved.

This leads to the question: is there a way to copy the tables I want to compare to a new database? Ideally, I could do this relatively easily using the GUI as opposed to bulk copy. Or, I suppose I could set the primary keys on the tables manually.

Let me know if you have any ideas. Thanks.

[Chris Auckland]

Thanks for your post.

When you do a select into, I think it only adds the basic column structure and doesn't add any other structural information like indexes, permissions etc. If you want to copy the complete structure of the table you could use the SQL Compare API to deploy the table to the target database. After you have deployed the structure of the table you can use the SQL Data Compare API for the data. The SQL Compare / SQL Data Compare UI or command line might be useful for this if you don't need to include it in your own application.

I hope this helps.
_________________
Chris