May 12, 2009 at 8:46 am
WebTechie38 (5/12/2009)
I really appreciate the responding guys, but I don't think SQL Server 2000 can do the except.
Yep, my bad. Sorry.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 8:49 am
Kishore.P (5/12/2009)
use TableDiff.exe which is exist in C:\Program files\Microsoft SQL Server\90\COM.
for more info see SQL BOL.
The "/90/" indicates SQL Server 2005, was it in SQL Server 2000 also?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 9:14 am
Okay. I made 2 copies of the AdventureWorks.Production.Product table in my SandBox database: dbo.Product and dbo.Product2. I then wrote the following code and ran it and had no records returned. I then changed one record in dbo.Product2 and reran the code and the code returned the two rows with a difference.
Take it and modify it as needed.
select
min(RecID) as RecID,
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
from
(
select
'L' as RecID,
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
from
dbo.Product
union all
select
'R',
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
from
dbo.Product2
) dt
group by
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
having
min(RecID) = 'R'
union
select
max(RecID) as RecID,
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
from
(
select
'L' as RecID,
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
from
dbo.Product
union all
select
'R',
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
from
dbo.Product2
) dt
group by
ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate
having
max(RecID) = 'L'
;
May 12, 2009 at 9:24 am
RBarryYoung (5/12/2009)
Kishore.P (5/12/2009)
use TableDiff.exe which is exist in C:\Program files\Microsoft SQL Server\90\COM.
for more info see SQL BOL.
The "/90/" indicates SQL Server 2005, was it in SQL Server 2000 also?
Based on BOL, nope, added in 2005. So I guess it isn't the way to go with 2000 although you could probably use it from a 2005 DEv edition install to compare tables on a 2000 server. I really need to get a 2000 VM up and running so I can test stuff like this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2009 at 9:43 am
Jack Corbett (5/12/2009)
RBarryYoung (5/12/2009)
Kishore.P (5/12/2009)
use TableDiff.exe which is exist in C:\Program files\Microsoft SQL Server\90\COM.
for more info see SQL BOL.
The "/90/" indicates SQL Server 2005, was it in SQL Server 2000 also?
Based on BOL, nope, added in 2005. So I guess it isn't the way to go with 2000 although you could probably use it from a 2005 DEv edition install to compare tables on a 2000 server. I really need to get a 2000 VM up and running so I can test stuff like this.
Still have a couple of servers with SQL Server 2000 installed, couldn't find it either.
May 12, 2009 at 11:09 am
WebTechie38 (5/12/2009)
You know I've met some great people on this forum and some I truly look up to as mentors.Then there are times people post things and you are not sure how to respond.
But if I understand the above query, and granted I am still learning, we are looking at one column (DepartmentID). That my friend is one column.
I was simply stating that I need to compare all the columns. In any event, I appreciate you taking the time to respond to my original question.
Tony
So you don't want to compare Primary keys and see if the non-key data is different? I confused on why you want to compare every column. Perhaps it is a terminology thing, but some sample data and expected output would help to clarify.
May 12, 2009 at 12:13 pm
Lamprey13 (5/12/2009)
WebTechie38 (5/12/2009)
You know I've met some great people on this forum and some I truly look up to as mentors.Then there are times people post things and you are not sure how to respond.
But if I understand the above query, and granted I am still learning, we are looking at one column (DepartmentID). That my friend is one column.
I was simply stating that I need to compare all the columns. In any event, I appreciate you taking the time to respond to my original question.
Tony
So you don't want to compare Primary keys and see if the non-key data is different? I confused on why you want to compare every column. Perhaps it is a terminology thing, but some sample data and expected output would help to clarify.
There's nothing odd about this, it's a typical way that Table Compares are frequently done. Some table compares are PK based, and some are based on the entire column set. The later does not have a "data changed" category, just "rows in table 1 that are not in table 2" and vice-versa.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 12:16 pm
You will note for instance that the 2005 EXCEPT and INTERSECT operators both compare all column values to determine the matched and unmatched rows (which is what makes them nice for this kind of request).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 2:42 pm
We use REDGATE SQL Data Compare. It is a little pricey but works great for any table that has a primary key.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply