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

SQL Code Snippet - Getting Accurate Years Elapsed Between Two Dates

Sometimes when coding it is important to be more granular than the DATEDIFF functionality allows for in SQL server.  In SQL server if you try to compare two dates, and those two dates happen on two different years, the "Years" between the dates will always be (Later Year - Earlier Year) depending on the order you use of course.

This can be bad sometimes.  For example, if you are trying to see if someone is 32 years old and is eligible for your special program or not, using the DATEDIFF functionality simply will not cut it, especially when the dates themselves are within days of the value. (IE trying to use 365 days as a rule of thumb doesn't work because of leap days).

Here is a method I wrote to handle this edge case, with comments.

DECLARE @BIRTHDATE datetime

DECLARE @DATE1 datetime

DECLARE @DATE2 datetime

SET @DATE1 = '02/02/2012'

SET @DATE2  = '01/30/2012'

SET @BIRTHDATE = '02/01/1980'

/*

The following shows how SQL server fails when determining the years between two dates.

*/

SELECT

 DATEDIFF(YYYY,@BIRTHDATE,@DATE1) as datediffabs   -- Should be 32, correct.

, DATEDIFF(YYYY,@BIRTHDATE,@DATE2) as datediffabs2 -- Should be 31 + 364 days

/*

Using the above, the following code step by step shows using both above examples and correctly determining the number of years

between the two dates.

*/

SELECT

 DATEDIFF(YYYY,@BIRTHDATE,@DATE1) as DATE1diff  -- The raw difference returned by SQL for the first claim date vs the birthdate

, DATEDIFF(YYYY,@BIRTHDATE,@DATE2) as Date2Diff -- The raw difference returned by SQL for the second claim date vs the birthdate

, case when MONTH(@DATE1) > MONTH(@BIRTHDATE) OR ( MONTH(@DATE1) = MONTH(@BIRTHDATE)  and DAY(@DATE1) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as DATE1Modifier  -- This is the underlying modifier being used for Date 1 to determine if it exceeds the birthdate

, case when MONTH(@DATE2) > MONTH(@BIRTHDATE) OR (  MONTH(@DATE2) = MONTH(@BIRTHDATE) and DAY(@DATE2) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as Date2Modifier  -- This is the underlying modifier being used for Date 2 to determine if it exceeds the birthdate

, DATEDIFF(YYYY,@BIRTHDATE,@DATE1) - case when MONTH(@DATE1) > MONTH(@BIRTHDATE) OR ( MONTH(@DATE1) = MONTH(@BIRTHDATE)  and DAY(@DATE1) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as ModifiedDATE1 -- The modified years since birth date for the first claim date

, DATEDIFF(YYYY,@BIRTHDATE,@DATE2) - case when MONTH(@DATE2) > MONTH(@BIRTHDATE) OR (  MONTH(@DATE2) = MONTH(@BIRTHDATE) and DAY(@DATE2) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as ModifiedDate2  -- The modified years since birth date for the second claim date

SQL Code Snippet - Concatonating data from a table

This is a bit of code, with my SQL server management server style comments, for concatonating a list of data from a table that shares a common key.  It has two examples.  One using a CTE and the other using a trick that may or may not work in future versions of SQL Server.

This creates input

myid	COMMENT
1	concat
1	concat2
1	concat3
1	test
2	concaxt
2	concaxt2
2	concaxt3
2	testx

And generates the output of:

myid	COMMENT
1	concat, concat2, concat3, test
2	concaxt, concaxt2, concaxt3, testx

/*
This example provides two methods for concatonating records.  The first method is a pure sql method that works with common table expressions.
The second uses a "trick" that can be done with the current versions of microsoft sql to concat records, it is much faster but may not be usable
in future versions.
*/
IF Object_id('tempdb..#tmp') IS NOT NULL
  BEGIN
      DROP TABLE #tmp
  END

CREATE TABLE #tmp
  (
     myid    INT,
     comment VARCHAR(255)
  )

INSERT INTO #tmp
            (myid,
             comment)
SELECT 1,
       'test'
UNION
SELECT 1,
       'concat'
UNION
SELECT 1,
       'concat2'
UNION
SELECT 1,
       'concat3'
UNION
SELECT 2,
       'testx'
UNION
SELECT 2,
       'concaxt'
UNION
SELECT 2,
       'concaxt2'
UNION
SELECT 2,
       'concaxt3'
/* 

Quick example on how to concatonate records with a CTE 
*/
;

WITH 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,
               Row_number()
                 OVER (
                   partition BY myid
                   ORDER BY comment ) 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 myid,
               CONVERT(NVARCHAR(max), comment) COMMENT,
               row_num
        FROM   rownumsfororder
        WHERE  row_num = 1
         UNION ALL
         SELECT c.myid,
                ( c.comment + ', ' + l.comment ) COMMENT,
                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),
     -- Now we want to sort so that we get the "deepest" of the recursively created records
     -- This "deepest" record will have the highest row number.  Again, something might be doable above to just simply
     -- choose only good records for this, if you know how to make this faster, send this email back with better examples.
     row_numedconcatenations
     AS (SELECT myid,
                comment,
                Row_number()
                  OVER (
                    partition BY myid
                    ORDER BY row_num DESC ) row_num
         FROM   concatenations),
     concatedlist
     AS (
        /* 
        This gets the concatonated comment 
        */
        SELECT *
         FROM   row_numedconcatenations
         WHERE  row_num = 1)
-- The final set you can then join to your main set of data or pipe into another temp table for a later join.
-- Enjoy! 
SELECT *
FROM   concatedlist

--- This is another, much faster, method of doing concatonations.  The only thing this does not have is flexibility 
-- in the type of concats you can do, IE you can do no transformations on the data like you can with the CTE 
-- recursion.  IF you just want to concat the records, but not prune the records in any way, then this is a good way to go.
-- This may not work in future versions.
SELECT x.myid,
       LEFT(x.comments, Len(x.comments) - 1) AS comments
FROM   (SELECT t1.myid,
               (SELECT comment + ','
                FROM   #tmp AS t2
                WHERE  t2.myid = t1.myid
                ORDER  BY comment
                FOR xml path('')) AS comments
        FROM   #tmp AS t1
        GROUP  BY t1.myid) AS x 

SQL Code Snippet - Check for and drop temp tables

Many times when doing development, I will see examples where people ran a piece of code multiple times, and then had drop table statements littering their code, usually commented out, but sometimes not (causing headaches when testing their code and having to return it because the table does not exist when you run it).

One of the easiest bits of code that you can add when testing, and is usually harmless to leave in your code overall, is the following:

IF object_id('tempdb..#tmp') IS NOT NULL

BEGIN

   DROP TABLE #tmp

END

The code checks for the existence of the table with the object_id given, and if it exists, drops it.

Extremely useful, but I am always surprised at how few people use this that do SQL development every day.

SQL code snippets - Left padding a character

So I have accumulated a bunch of sql code snippets and tricks in my time as a developer, and I figured I could share some of the more useful ones.

If you ever wanted to be able to pad characters quickly in sql, here is some code for that.  Padding characters is when you want to take some arbitrary sized value, and "pad" or add characters to that value.  The following will add padding on the left of a string value.

/*
Two methods for padding a character, they both assume left padding.
*/ 
SELECT RIGHT(Replicate(@padchar, @len) + @str, @len) 

SELECT Stuff(@str, 1, 0, Replicate('0', @n - Len(@str))) 

select right(replicate('0', 10) + 'four', 10)

select stuff('four', 1, 0, replicate('0', 10 - len('four')))

Produces 000000four and 000000four as the output.