Getting row differences - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting row differences

Author

Topic

notmyrealname

98 Posts

Posted - 2009-01-12 : 15:56:52

Hi.

I don't think this can be done but i will ask anyway.

I am able to determine which rows are different between two similar tables using a simple UNION ALL/GROUP BY statement. What i am trying to do is list the actual changes between the different rows. I don't really need to do all of this in one shot. But what i am trying to do is this:

- Take two rows
- Loop through the fields of the two rows and return the fieldnames
of the fields that are different.

Col1 Col2 Col3 Col4
------------------------
1 1 1 1
1 2 1 6

- What i've been trying to do is accumulate a variable result while
i loop through the columns. In this case i would like to return:
Col2(1,2):Col4(1,6)

The problem i am having is executing a dynamic select statement that stores a variable.

I.E.
EXEC('SELECT (SELECT ' + @col_Name + ' FROM tbl_Material WHERE (WorkOrder = ' + @wo_No + ') AND (ID = ' + @prt_ID + ')) AS ' + @prt_Value_E2P)

The @col_Name is the name of the column that is being compared and the @wo_No and @prt_ID are two parameters that return a single row. I am trying to save the result to the @prt_Value_E2P variable and this is where i am having the most trouble. I was hoping to compare the specific column values from both rows and create a text string showing the differences.

I hope this is enough info. Any help would be greatly appreciated.

Thanks,
Joel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 00:18:57

can i ask purpose for doing this?wouldnt it be enough to list records that differ in any one of column values, just using left join

notmyrealname

98 Posts

Posted - 2009-01-13 : 07:34:34

Hi. Thanks for the reply.

The reason i want to return the actual changes is to inform the user what the exact changes are that were made so that he can either accept them or reject them. Simply saying that this row and that row are different is not sufficient. I am creating the app in VB.NET and am sure that i can handle this matter there but i am trying to do as much as possible with SQL.

The structure of the tables is constant so i could hard code the columns that i am comparing as opposed to stepping through each column. I was only trying to loop through the columns to create a generic reusable procedure.

Hope this clears things up.

Thanks,
Joel

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 11:56:35

You can use except.
SELECT a, b
FROM Table1
except
select a,b from Table2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 11:58:09

quote:
Originally posted by darkdusky

You can use except.
SELECT a, b
FROM Table1
except
select a,b from Table2

still it will provide you enter resultset (values) which differs from first table rather than giving column names where value differs. OP is looking for latter case.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 12:14:13

[code]--backup log ahl with truncate_only
DECLARE @Sample TABLE
(
pk INT,
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT
)

INSERT @Sample
SELECT -1, 1, 1, 1, 1 UNION ALL
SELECT -3, 3, 2, 6, 1 UNION ALL
SELECT -2, 1, 2, 1, 6

SELECT STUFF(d.r, 1, 2, '') AS theColumns
FROM (
SELECT ', ' + u.theCol
FROM @Sample AS s
UNPIVOT (
theValue
FOR theCol IN (s.Col1, s.Col2, s.Col3, s.Col4)
) AS u
WHERE u.pk IN (-1, -2)
GROUP BY u.theCol
HAVING MIN(u.theValue) < MAX(u.theValue)
FOR XML PATH('')
) AS d(r)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources