mish_b20 (6/4/2008)
Hello ,In order to see if the tables aren't identical you don't have to make so sophisticated script.
My suggestion to you is like this :
declare @data table( Table1 nvarchar(max), Table2 nvarchar(max) )
insert into @data
(Table1,Table2)
select
(select * from
db1.dbo.GalleryCategories as tbl
for xml auto
) as Table1,
(select * from
db2.dbo.GalleryCategories as tbl
for xml auto
) as Table2
SELECT CASE
WHEN count(TablesDataCompare.Table1)>0 THEN 'Yes'
ELSE 'No'
END
[Is Identical Tables?]
FROM
(SELECT Table1,Table2
FROM @data
WHERE Table1=Table2
) TablesDataCompare
When running the script you will see if the tables data are identical or not identical
Good luck.
Michael
🙂
Thanks mish_b20.
However, and not to be too critical or your suggestion, the first line of my article stated "I wanted to be able to compare the table data in two databases where I was not able to transfer the data or install a program (such as Red-Gate's SQL Data Compare) but I could use a script." I assumed that one would understand that I could not read one from the other as well. Many of these tables may have hundreds of millions of rows. In a system where one can get at both pieces of data, Red-Gate or DBGhost or other tools do a really great job of both schematic and data comparisons. However, this works when you can't use those types of things.
Not every problem is a nail, or at least that is the excuse I give my wife when I buy a new tool... 🙂 She can't understand why a butter knife doesn't constitute a complete tool box. 😀