• Brandie Tarvin (5/13/2013)


    So, any ideas?

    Brandie, Try this out:

    -- Let's add a column to the staging table to make deletes easier. You could even index this if you want to.

    -- However, this isn't necessary.

    IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Staging','U') AND name = 'DateField')

    ALTER TABLE dbo.Staging ADD DateField DATE;

    -- this is necessary only to prevent any other updates to the Summary table until all of the inserts have taken place.

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    -- get the vendor records assigned first, and assign sequential numbers for the UNID

    WITH cte AS

    (

    SELECT ID, UNID, Extract_Record, DateField, ROW_NUMBER() OVER (ORDER BY ID) AS RN

    FROM dbo.Staging

    WHERE Extract_Record LIKE '2%' -- get the vendor data

    )

    UPDATE cte

    SET UNID = RN,

    DateField=LTRIM(RTRIM(Substring(Extract_Record, 97,6)))+'01';

    -- now assign the details to the same UNID for the vendor record < this current record

    -- if you don't want to add the DateField column to the Staging table, you can remove

    -- the 2nd CROSS APPLY and the update to that column.

    UPDATE t1

    SET UNID = t2.UNID,

    DateField = t3.DateField

    FROM dbo.Staging t1

    CROSS APPLY (SELECT MAX(UNID) FROM dbo.Staging WHERE ID < t1.ID) t2(UNID)

    CROSS APPLY (SELECT DateField FROM dbo.Staging WHERE UNID = t2.UNID) t3

    WHERE t1.Extract_Record LIKE '3%';

    -- delete the current month:

    -- Again, if you don't want to use the DateField column, just use your existing delete statement

    -- DELETE FROM dbo.Staging where DateField = CONVERT(DATETIME, CONVERT(char(6),GETDATE(),112) + '01')

    BEGIN TRANSACTION

    DECLARE @max-2 INT;

    SELECT @max-2=ISNULL(MAX(TransactionID),0) FROM dbo.Summary;

    -- the repeatable read prevents any other transaction from inserting data that would change the max setting

    -- I need to control the value of the identity column, so that I can associate the identity value with the UNID value for the summary record.

    -- The association is the @max-2 from above with the UNID already calculated for this record.

    SET IDENTITY_INSERT dbo.Summary ON;

    INSERT INTO dbo.Summary( TransactionID, Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9)

    SELECT @max-2+UNID,

    LTRIM(RTRIM(Substring(Extract_Record, 2,4))) AS Col1,

    LTRIM(RTRIM(Substring(Extract_Record, 6,10))) AS Col2,

    LTRIM(RTRIM(Substring(Extract_Record, 16,3))) AS Col3,

    LTRIM(RTRIM(Substring(Extract_Record, 19,20))) AS Col4,

    LTRIM(RTRIM(Substring(Extract_Record, 39,8))) AS Col5,

    LTRIM(RTRIM(Substring(Extract_Record, 47,10))) AS Col6,

    LTRIM(RTRIM(Substring(Extract_Record, 57,10))) AS Col7,

    LTRIM(RTRIM(Substring(Extract_Record, 67,30))) AS Col8,

    LTRIM(RTRIM(Substring(Extract_Record, 97,8))) AS Col9

    FROM dbo.Staging ri

    LEFT OUTER JOIN Summary rit

    ON LTRIM(RTRIM(Substring (ri.Extract_Record, 2,4))) = LTRIM(RTRIM(rit.Col1))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 6,10))) = LTRIM(RTRIM(rit.Col2))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 16,3))) = LTRIM(RTRIM(rit.Col3))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 19,20)))= LTRIM(RTRIM(rit.Col4))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 39,8))) = LTRIM(RTRIM(rit.Col5))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 47,10)))= LTRIM(RTRIM(rit.Col6))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 57,10)))= LTRIM(RTRIM(rit.Col7))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 67,30)))= LTRIM(RTRIM(rit.Col8))

    AND LTRIM(RTRIM(Substring(ri.Extract_Record, 97,8)))= LTRIM(RTRIM(rit.Col9))

    WHERE rit.TransactionID IS NULL

    -- TransactionID is a PK -->> NOT NULL. A null here means that no matching record was found, so it's okay to insert this record.

    -- No need to compare every column.

    AND ri.Extract_Record LIKE '2%'; -- just the "2" records

    -- don't forget to turn IDENTITY INSERT off!

    SET IDENTITY_INSERT dbo.Summary OFF;

    INSERT INTO Detail (TransactionID,

    Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,

    Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16)

    SELECT @max-2+UNID, -- since we already have the UNID matching in the table, and we inserted UNID+@Max into the summary, use this same calc for the detail.

    LTRIM(RTRIM(Substring(Extract_Record, 2,4))) AS Col1,

    LTRIM(RTRIM(Substring(Extract_Record, 6,4))) AS Col2,

    LTRIM(RTRIM(Substring(Extract_Record, 10,20))) AS Col3,

    LTRIM(RTRIM(Substring(Extract_Record, 30,8))) AS Col4,

    LTRIM(RTRIM(Substring(Extract_Record, 38,24))) AS Col5,

    LTRIM(RTRIM(Substring(Extract_Record, 62,8))) AS Col6,

    LTRIM(RTRIM(Substring(Extract_Record, 70,8))) AS Col7,

    LTRIM(RTRIM(Substring(Extract_Record, 78,24))) AS Col8,

    LTRIM(RTRIM(Substring(Extract_Record, 102,10))) AS Col9,

    LTRIM(RTRIM(Substring(Extract_Record, 112,4))) AS Col10,

    LTRIM(RTRIM(Substring(Extract_Record, 116,10))) AS Col11,

    LTRIM(RTRIM(Substring(Extract_Record, 126,4))) AS Col12,

    LTRIM(RTRIM(Substring(Extract_Record, 130,24))) AS Col13,

    LTRIM(RTRIM(Substring(Extract_Record, 154,3))) AS Col14,

    LTRIM(RTRIM(Substring(Extract_Record, 157,17))) AS Col15,

    LTRIM(RTRIM(Substring(Extract_Record, 174,5))) AS Col16

    FROM dbo.Staging ri2

    LEFT OUTER JOIN dbo.Detail rit2

    ON LTRIM(RTRIM(Substring(ri2.Extract_Record, 2,4))) = LTRIM(RTRIM(rit2.Col1))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 6,4))) = LTRIM(RTRIM(rit2.Col2))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 10,20))) = LTRIM(RTRIM(rit2.Col3))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 30,8))) = LTRIM(RTRIM(rit2.Col4))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 38,24)))= LTRIM(RTRIM(rit2.Col5))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 62,8))) = LTRIM(RTRIM(rit2.Col6))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 70,8))) = LTRIM(RTRIM(rit2.Col7))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 78,24))) = LTRIM(RTRIM(rit2.Col8))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 102,10))) = LTRIM(RTRIM(rit2.Col9))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 112,4))) = LTRIM(RTRIM(rit2.Col10))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 116,10))) = LTRIM(RTRIM(rit2.Col11))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 126,4))) = LTRIM(RTRIM(rit2.Col12))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 130,24))) = LTRIM(RTRIM(rit2.Col13))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 154,3))) = LTRIM(RTRIM(rit2.Col14))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 157,17)))= LTRIM(RTRIM(rit2.Col15))

    AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 174,5))) = LTRIM(RTRIM(rit2.Col16))

    WHERE rit2.TransactionDetailID IS NULL

    -- TransactionDetailID is PK -->> NOT NULL. Null here means no matching record was found, so it's okay to insert.

    -- No need to compare every column

    AND ri2.Extract_Record LIKE '3%' -- just the "3" rows

    COMMIT TRANSACTION

    SELECT * FROM dbo.Staging ORDER BY ID;

    SELECT * FROM dbo.Summary;

    SELECT * FROM dbo.Detail;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2