Monday, February 4, 2013

SQL Code Snippets - Line by Line as Bulk


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