December 29, 2007 at 11:38 pm
Comments posted to this topic are about the item Generate Scripts to Compare Tables
February 6, 2008 at 4:34 am
Absinthe, great script and very timely. I am migrating databases between data centres which are ring fenced so using 3rd party tools to compare could be problematic.
The auditors require us to prove the data is identical after the move and your script, though slightly manual in that a cut and paste is required should suit our requirements well.:D
cheers
george
---------------------------------------------------------------------
February 6, 2008 at 6:33 am
Some of the greatest things are created out of necessity. I normally use SQL Compare and SQL Data compare, but in the specific scenario, I was unable to do so, glad it helps someone else...
June 4, 2008 at 5:39 pm
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
🙂
June 5, 2008 at 8:02 am
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. 😀
September 7, 2011 at 6:49 am
Works fine unless your tables are in different schemas. Easy fix, but could have been fixed before sharing...
May 12, 2016 at 7:03 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply