This code does the following.
It emulates the behavior of a line by line (cursor based) importer using bulk commands when inserting into a transaction table.
If your line by line importer would have created multiple transactions, each cascading on top of the previous transaction
this can emulate that behavior, using whatever business rules that you desire.
Here, for example, the business rule is that if the previous record had a value, and the new record is a null value
then the previous value takes precedence, but if the new record has a value, it takes precedence, regardless of the
previous value.
Theoretically, with a clever case statement in the recursive step, you could easily generate a situation where
if the previous value was of higher value than a "new" value, you could "keep" the previous value. I leave this
to be implemented by the developer.
This code creates 2 sets of data. It creates a previous state profile transaction, IE, a table with multiple transaction,
each updated/imported using a line by line or this same bulk importer, with two items already in it.
ordernum represents the order, in the real version this would be transaction id, which would be unique, and correctly
orderd via the inserts into a source or some other ordering table.
The code then uses a CTE to get the latest of the transaction records, and uses that as the basis of the first
"merge" transaction that is created (the select before the UNION in the recursive CTE step).
Note the use of the left join, this makes it so that the myid=2 record is still merged into the data set correctly, since
no imports for that ID had yet been done.
The recursive step then unions each subsequent set of values, in the proper order, and produces the result, including
all intermediate results as if each row had been merged individually using a line by line importer.
IE
if the start set were:
1,2,null
1,3,4
1,4,null
Each intermediate step would be as such
1,2,null
1,3,4
1,4,4
I had a need to write this because of a situation where if an unique id came in multiple times, I wanted to be
able to capture each individual previous step to keep consistent. This code is now simplified for your use if you also have such a merge need.
IF you were going to do this behavior similar to a an audit table, IE you have a table with a single entry,
and then an audit table with multiple entries, you can insert all of the values into a temp table.
Then "insert" the value of the current table as well as the all but the latest values into the audit table, and then
update the table with the latest value.
IE in the 1,2,null example.
If the single value in the table it is in starteed as
1,2,null
YOu would insert 1,2,null into the audit table
1,3,4 into the audit table
and update the main table with 1,4,4
Hope this helps!
*The output below selects from two tables, the ordered original table, and the line by line table so that you can
verify the results.
Original input data, with ordernum as the input order, this is an import from the first "file."
myid
|
ordernum
|
comment
|
commenttwo
|
commentthree
|
1
|
1
|
test
|
NULL
|
test1
|
1
|
2
|
concat
|
NULL
|
concat4
|
1
|
3
|
concat2
|
concat4
|
concat4
|
1
|
4
|
concat3
|
concat6
|
concat3
|
1
|
5
|
concat2
|
NULL
|
concat3
|
2
|
6
|
testx
|
NULL
|
NULL
|
2
|
7
|
concaxt
|
NULL
|
concaxt
|
2
|
8
|
concaxt2
|
notnull
|
notnull
|
2
|
9
|
concaxt3
|
NULL
|
concaxt3
|
Data for the second "file" imported:
myid
|
ordernum
|
comment
|
commenttwo
|
commentthree
|
1
|
1
|
FromImport1
|
NULL
|
NULL
|
1
|
2
|
FromImport2Update
|
AlsoFromImport2
|
AlsoFromImport2
|
Output data, the records are now showing, transactionally, as they would if inserted/updated using a line by line processor.
myid
|
comment
|
commenttwo
|
commentthree
|
row_num
|
1
|
test
|
AlsoFromImport2
|
test1
|
1
|
1
|
concat
|
AlsoFromImport2
|
concat4
|
2
|
1
|
concat2
|
concat4
|
concat4
|
3
|
1
|
concat3
|
concat6
|
concat3
|
4
|
1
|
concat2
|
concat6
|
concat3
|
5
|
2
|
testx
|
NULL
|
NULL
|
1
|
2
|
concaxt
|
NULL
|
concaxt
|
2
|
2
|
concaxt2
|
notnull
|
notnull
|
3
|
2
|
concaxt3
|
notnull
|
concaxt3
|
4
|
IF object_id('tempdb..#transaction')
IS NOT NULL
BEGIN
DROP TABLE #transaction
END
IF object_id('tempdb..#tmp') IS NOT NULL
BEGIN
DROP TABLE #tmp
END
CREATE TABLE #transaction
(
myid int
, ordernum int
, comment varchar(255)
, commenttwo
varchar(255)
,
commentthree varchar(255)
)
CREATE TABLE #tmp
(
myid int
, ordernum int
, comment varchar(255)
, commenttwo
varchar(255)
,
commentthree varchar(255)
)
INSERT INTO #transaction
(
myid
,ordernum
, comment
, commenttwo
,
commentthree
)
select
1,1, 'FromImport1',null,null
UNION
select
1,2, 'FromImport2Update','AlsoFromImport2','AlsoFromImport2'
INSERT INTO #tmp
(
myid
,ordernum
, comment
, commenttwo
,
commentthree
)
select
1,1, 'test',null,'test1'
UNION select
1,2, 'concat', null,'concat4'
UNION select
1,3, 'concat2','concat4','concat4'
UNION select
1,4, 'concat3','concat6', 'concat3'
UNION select
1,5, 'concat2',NULL, 'concat3'
UNION
select
2,6, 'testx',null,null
UNION select
2,7, 'concaxt',null, 'concaxt'
UNION select
2,8, 'concaxt2','notnull','notnull'
UNION select
2,9, 'concaxt3',null,'concaxt3'
SELECT * FROM #tmp
;WITH
cte_getlatestprofileytransaction AS
(
SELECT
*
FROM
(
SELECT
*
,row_number() over(partition by pint.myid order by ordernum desc) row_num
FROM #transaction pint
) example
WHERE row_num = 1
),
rownumsfororder as
(
/* Sort the
records in the order you want to concatonate them for there id in this case we use row num!*/
select
myid,
comment ,
commenttwo,
commentthree
, ROW_NUMBER() OVER (
PARTITION
BY myid
ORDER BY ordernum asc
) row_num
FROM #tmp
)
,
Concatenations AS
(
--Recursively
concactonate the records in the order defined above, this makes lots of records
and makes this super slow! I bet
-- there is a
trick somewhere that can do this faster!
SELECT
rnfo.myid,
isnull(rnfo.comment,cglpi.comment) comment,
isnull(rnfo.commenttwo,cglpi.commenttwo) commenttwo,
isnull(rnfo.commentthree,cglpi.commentthree) commentthree,
rnfo.row_num
FROM
rownumsfororder rnfo
LEFT JOIN
cte_getlatestprofileytransaction cglpi
ON rnfo.myid
= cglpi.myid
WHERE
rnfo.row_num
= 1
UNION ALL
SELECT
c.myid,
isnull(l.comment , c.comment) comment,
isnull(l.commenttwo , c.commenttwo) commenttwo,
isnull(l.commentthree , c.commentthree) commentthree,
l.row_num
FROM Concatenations c --
this is a recursion!
INNER JOIN rownumsfororder l ON
l.myid = c.myid
AND l.row_num = c.row_num + 1
)
select
*
FROM
Concatenations
ORDER BY myid,row_num
No comments:
Post a Comment