Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate Scripts to Compare Tables


Generate Scripts to Compare Tables

Author
Message
Absinthe-428914
Absinthe-428914
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 109
Comments posted to this topic are about the item Generate Scripts to Compare Tables
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
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.BigGrin

cheers

george

---------------------------------------------------------------------
Absinthe-428914
Absinthe-428914
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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...
mish_b20
mish_b20
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Smile
Absinthe-428914
Absinthe-428914
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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
Smile


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... Smile She can't understand why a butter knife doesn't constitute a complete tool box. BigGrin
Matt Wright
Matt Wright
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 210
Works fine unless your tables are in different schemas. Easy fix, but could have been fixed before sharing...



I am Melvis.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8648 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search