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