Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate Scripts to Compare Tables Expand / Collapse
Author
Message
Posted Saturday, December 29, 2007 11:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, Visits: 109
Comments posted to this topic are about the item Generate Scripts to Compare Tables
Post #437374
Posted Wednesday, February 6, 2008 4:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
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


---------------------------------------------------------------------

Post #452122
Posted Wednesday, February 6, 2008 6:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, Visits: 109
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...
Post #452164
Posted Wednesday, June 4, 2008 5:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 18, 2011 7:12 AM
Points: 6, Visits: 81
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
:)
Post #511863
Posted Thursday, June 5, 2008 8:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, Visits: 109
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. :D
Post #512141
Posted Wednesday, September 7, 2011 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:25 PM
Points: 11, Visits: 179
Works fine unless your tables are in different schemas. Easy fix, but could have been fixed before sharing...



I am Melvis.
Post #1171035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse