Data Versioning - 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)

Data Versioning

Author

Topic

nbrianyc
Starting Member

3 Posts

Posted - 2009-06-19 : 05:42:41

Question:
Is it possible to use CTE to recursively return the set of record(s) NOT in the recursive CTE?

Background:
The versioning strategy is to have an initial set of records represent version #1. When a change is made to any of the records in version #1, a new version will be created by querying ONLY the modified records from version #1 and re-inserting them as version #2, thereby making this new version related to the old.

Criteria:
* there can be multilpe versions
* a version can "inherit" records ONLY from an earlier version

Since there is some kind of hierarchy of versions, essentially, the CTE needs to "pick up" the records from a given version and find the non-modified records up the version hierarchy until all data of the version are collected.

Heres a sample concept:
DECLARE @newVersionNumber DECIMAL(2,1)
DECLARE @baseVersionID INT
DECLARE @newVersionID INT

DECLARE @table TABLE (
TableID INT IDENTITY(1,1)
,[Name] VARCHAR(10)
,[Value] DECIMAL(18,2)
,VersionID INT)
DECLARE @version TABLE (
VersionID INT IDENTITY(1,1)
,VersionNumber DECIMAL(2, 1)
,ParentVersionID INT)

-- VERSION # 1: Initial state of the data with default version
SET @baseVersionID = 1
INSERT @version VALUES (1.0, Null)
INSERT @table ([Name], [Value], VersionID) VALUES ('A', 100, @baseVersionID) -- never change in all version
INSERT @table ([Name], [Value], VersionID) VALUES ('B', 200, @baseVersionID) -- changed in every version
INSERT @table ([Name], [Value], VersionID) VALUES ('C', 300, @baseVersionID); -- changed in every other version

-- VERSION # 2: Simulate a versioning of the data based on default version
SET @baseVersionID = 1
SELECT @newVersionNumber = IsNull(VersionNumber, 0) + 1 FROM @version WHERE VersionID = @baseVersionID
INSERT @version VALUES (@newVersionNumber, @baseVersionID)
SET @newVersionID = SCOPE_IDENTITY()
INSERT @table([Name], [Value], VersionID)
(
SELECT DISTINCT [Name], 400, @newVersionID FROM @table WHERE [Name] = 'B'
);

-- VERSION # 3: Simulate a versioning of the data
SET @baseVersionID = 2
SELECT @newVersionNumber = IsNull(VersionNumber, 0) + 1 FROM @version WHERE VersionID = @baseVersionID
INSERT @version VALUES (@newVersionNumber, @baseVersionID)
SET @newVersionID = SCOPE_IDENTITY()
INSERT @table([Name], [Value], VersionID)
(
SELECT DISTINCT [Name], 800, @newVersionID FROM @table WHERE [Name] = 'B'
UNION
SELECT DISTINCT [Name], 900, @newVersionID FROM @table WHERE [Name] = 'C'
);

-- VERSION # 4: Simulate a versioning of the data
SET @baseVersionID = 2
SELECT @newVersionNumber = IsNull(VersionNumber, 0) + 1 FROM @version WHERE VersionID = @baseVersionID
INSERT @version VALUES (@newVersionNumber, @baseVersionID)
SET @newVersionID = SCOPE_IDENTITY()
INSERT @table([Name], [Value], VersionID)
(
SELECT DISTINCT [Name], 200, @newVersionID FROM @table WHERE [Name] = 'A'
UNION
SELECT DISTINCT [Name], 1600, @newVersionID FROM @table WHERE [Name] = 'B'
UNION
SELECT DISTINCT [Name], 1800, @newVersionID FROM @table WHERE [Name] = 'C'
);

-- VERSION # 5: Simulate a versioning of the data
SET @baseVersionID = 2
SELECT @newVersionNumber = IsNull(VersionNumber, 0) + 1 FROM @version WHERE VersionID = @baseVersionID
INSERT @version VALUES (@newVersionNumber, @baseVersionID)
SET @newVersionID = SCOPE_IDENTITY()
INSERT @table([Name], [Value], VersionID)
(
SELECT DISTINCT [Name], 3600, @newVersionID FROM @table WHERE [Name] = 'C'
);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 02:28:57

your sample code doesnt make much sense.Did nt understand why you pass base vesrion id=2 for all the other versions (2,3,4,5). Also whats the rule for generating the values 400,800,900,...?

nbrianyc
Starting Member

3 Posts

Posted - 2009-06-21 : 20:15:16

Visakh

thanks for the response. actually those numbers are just input data. What its supposed to do is generate an initial version of data. basically a version is just a set of records. any changes to an existing (version #1) can be saved as a new version (version#2). This new version will contain only those records that was modified from the former version. versions (3,4,5) is based on version#2 that is why "versiondid = 2".

now i'm looking for a way to get the non-modified records from the parent version and the versioning can be may levels deep.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 10:39:52

so you want capture the unchanged records after each version change?

nbrianyc
Starting Member

3 Posts

Posted - 2009-06-22 : 20:46:23

You are correct. But I need to capture both the changed and unchanged records. I was wondering if this can be done on a single CTE, where it will collect these records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources