February 8, 2010 at 5:28 am
Hi,
We have a table where we store versions of data and mark with some version indicator. Typically we are having around 50 columns in this table.
We got a requiremnt to identify which column got changed comparing two verisions for a given primary key.
Is there any way we can identify this in sql server?
Pls help
February 8, 2010 at 7:01 am
suneel-210238 (2/8/2010)
Hi,We have a table where we store versions of data and mark with some version indicator. Typically we are having around 50 columns in this table.
We got a requiremnt to identify which column got changed comparing two verisions for a given primary key.
Could you provide example data for this please? I assume each time data is changed you actually create a new row in the table, with something to show version numbers. However, your mention of identifying versions against a Primary Key makes it puzzling. A primary key has to be unique, so there must be something else to identify 'linked' rows.
An example will make this a lot clearer.
BrainDonor.
February 8, 2010 at 7:34 am
Brain..
Thanks for Response..
Here is the example..
Version Table where we store the Version.
Ours is a product respository project and every 15 days we refresh all the table data.
The new requirement is we just want to send only data that is modified that day in the form of delta.
For example:
ProductTable ( ProductID, UpdateDateTM )
ProductID ProductName ProductDesc PartNo UpdateTM Version
1 P1 P1-Desc P1-Prt Feb-2 2am V1
1 P1 P1-Desc P1-Prt Feb-2 4am V1
1 P1 P1-Desc P1-Prt Feb-2 6am V1
------------------
1 P1 P1-Changed P1-Prt Feb-3 6am V2
2 P2 P2-Desc P2-Prt Feb-3 6am V2
3 P3 P3-Desc P3-Prt Feb-3 6am V2
In this case we want to compare latest of ProductID between V1 and V2 and send the updates if it already existed and inserts if it new..so that o/p should be
Update CleintProduct Set ProductDesc = 'P1-Changed' Where ProductID = 1
Insert Into CleintProduct (2,'P2','P2-Desc','P2-Prt')
Insert Into CleintProduct (3,'P3','P3-Desc','P3-Prt')
Hope this helps!!
February 8, 2010 at 8:39 am
February 8, 2010 at 9:07 am
Brain,
I apologize. Hope this reply will make ur monday gr8.
Structure of Table
ProductTable
(
ProductID int,
ProductName varchar(20),
ProductDesc varchar(50),
PartNo Varchar(5),
UpdateTM DateTime,
Version Varchar(10),
)
The PrimaryKey on Table are ProductID and UpdateTM columns.
We also maintain the history in the same table. So every time a change is made on this table we insert a record but we donot update the record.
While displaying, we pull the latest record by doing order by on UpdateTM for a given ProductID.
I should mention it as Logical Primary Key.
--------
If I have to put it in a simple terms...
There is a table with 40 columns and there are two rows in that table. Can we know what non-key columns are having difference and what is that value?
February 8, 2010 at 11:35 am
I don't know if there's an easier way to do it but here's what I came up with (using UNPIVOT and PIVOT):
DECLARE @tbl TABLE
(id INT,
c1 CHAR(1), c2 CHAR(1), c3 CHAR(1), c4 CHAR(1), c5 CHAR(1),
c6 CHAR(1), c7 CHAR(1), c8 CHAR(1), c9 CHAR(1)
)
INSERT INTO @tbl
SELECT 1,'a','b','c','d','e','f','g','h','i' UNION ALL
SELECT 2,'a','b','g','d','e','f','g','d','i' UNION ALL
SELECT 3,'a','b','g','d','e','f','g','d','i'
DECLARE @old INT
DECLARE @new INT
-- change those values to the rows you want to compare
SET @old = 1
SET @new = 3
; WITH cte AS -- build a table based on @old and @new
(
SELECT 1 AS pos, @old AS val UNION ALL
SELECT 2, @new
)
, cte1 AS -- get the two relevant rows including the value to define old/new
(
SELECT pos,t.*
FROM @tbl t
INNER JOIN cte ON val = id
)
, cte2 AS -- unpivot the data
(
SELECT pos, cols, vals
FROM
(
SELECT *
FROM cte1
WHERE pos IN(1,2)
) AS p
UNPIVOT
( vals FOR cols IN (c1,c2,c3,c4,c5,c6,c7,c8,c9) -- add your columns to be compared
) AS unpvt
)
SELECT @old AS IdOld,@new AS IdNew,cols,[1] AS OLD,[2] AS NEW -- pivot the data to compare the two rows
FROM
(SELECT * FROM cte2 ) p
PIVOT
(
MAX (vals)
FOR pos IN ([1], [2])
) AS pvt
WHERE [1]<>[2] -- exclude identical values
/* results
IdOldIdNewcolsOLDNEW
13c3cg
13c8hd
*/
February 8, 2010 at 9:56 pm
How many different products?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2010 at 3:38 am
Hi,
we have around 2000 products and there will be more coming.
--But I found a soln pls let me know if it is feasible.
I added new column called rowid of datatype int and it is a identitycolumn.
Declare @sql nvarchar(max)
Set @sql = ''
--Prepare the dynamic sql
Select @sql = 'case when cast(a.'+name+' as varchar) <> case when cast(b.'+name+' as varchar) then cast(b.'+name+' as varchar) else '''' end, '
From sys.columns where object_name(object_id) = <TableName>
Select @sql = @sql + 'From <tablename> a Inner Join <tableName> b
on a.Pkey1 = b.Pkey1
and a.rowid=1 and b.rowid=2
I cannot copy the code but here is the high level code that returns the data that is different.
February 9, 2010 at 5:45 am
suneel-210238 (2/9/2010)
Hi,we have around 2000 products and there will be more coming.
--But I found a soln pls let me know if it is feasible.
I added new column called rowid of datatype int and it is a identitycolumn.
Declare @sql nvarchar(max)
Set @sql = ''
--Prepare the dynamic sql
Select @sql = 'case when cast(a.'+name+' as varchar) <> case when cast(b.'+name+' as varchar) then cast(b.'+name+' as varchar) else '''' end, '
From sys.columns where object_name(object_id) = <TableName>
Select @sql = @sql + 'From <tablename> a Inner Join <tableName> b
on a.Pkey1 = b.Pkey1
and a.rowid=1 and b.rowid=2
I cannot copy the code but here is the high level code that returns the data that is different.
Tha's kind of where I was going with this.
You have to compare each column between two rows on way or another. The way you have above should work but if you run into performance problems with it, post back.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply