Comparing two tables in SQL Server

Post #251,111 by lincoln 4/5/06 2:12:40 PM Reply	Comparing two tables in SQL Server I have two tables which were identical except that one of them had a date column that the other didn't. Today the boss said to add the same date column to the table that was missing it. I received the latest datafile from the IBM and loaded it; afterwards, I did a "SELECT COUNT()" and saw that the row counts are identical. Is there any utility within SQL Server that will compare the data between the two tables and show me if any rows are not identical? I don't see anything in the Books Online that will do what I want. Is there any third party tools that might do this? Manually comparing two tables of 88K rows is out of the question. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. I am not merely a "consumer" or a "taxpayer". I am a Citizen* of the United States. [link\|mailto:bconnors@ev1.net\|contact me]
Post #251,112 by drewk 4/5/06 2:18:11 PM Reply	If you don't have any DB tools What I've done is just dump the entire contents to text files and diff the two. Not as pretty as the DB tools (which I have seen) but it takes about a minute to check at least the scope of your work. Best case the files are identical and you're done. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #251,113 by ChrisR 4/5/06 3:02:34 PM Reply	Write a query? No utility that I know of offhand, but writing a query to find differences is usually the tactic I employ. You can either do it one column at a time like: SELECT *\nFROM\n table1 one\n LEFT OUTER JOIN table2 two ON(two.key = one.key)\nWHERE\n (one.Column1 != two.Column2) OR\n ((one.Column IS NULL) AND (two.Column IS NOT NULL)) OR\n ((one.Column IS NOT NULL) AND (two.Column IS NULL)) Or you can just stack up a bunch of tests in the WHERE clause.
Post #251,116 by JayMehaffey 4/5/06 3:24:31 PM Reply	Re: Comparing two tables in SQL Server I've used the products from [link\|http://www.red-gate.com\|Red-gate] and they work pretty well but they are not cheap. If you want something freeware, search on "SQL compare" and pick one. Jay
Post #251,145 by Simon_Jester 4/5/06 7:06:48 PM Reply	Dumb question - does SQL Server have a BCP out command? (new thread) Created as new thread #251144 titled [link\|/forums/render/content/show?contentid=251144\|Dumb question - does SQL Server have a BCP out command?]

Welcome to IWETHEY!