December 17, 2009 at 7:24 am
Hi,
I have a staging table (dbo.LSR_Prepare) which is used to format/validate data then load into dbo.LSR_Pass. The dbo.LSR_Pass table contains over 3 million records with a primary key (PK) consisting of 5 fields. When loading, I need to check against the 1st 4 of these key fields.
(The 5th key field has a tinyint data-type with a default value of 1)
•If a match is found on the 1st 4 keys I have to increment the 5th key field by 1 and add the record
•Otherwise the record is added and the default of 1 is applied to the 5th key field
What is the easiest way to do this?
Thanks in advance,
Neal
December 17, 2009 at 8:05 am
Thanks all but I think I've got it.
For the matched key it's something like..
INSERT INTO dbo.LSR_Pass
(
K1,
K2,
K3,
K4,
K5
)
SELECT
B.K1,
B.K2,
B.K3,
B.K4,
MAX(A.K5) + 1
FROM dbo.LSR_Prepare B JOIN dbo.LSR_Pass A ON
A.K1 = B.K1 AND
A.K2 = B.K2 AND
A.K3 = B.K3 AND
A.K4 = B.K4
GROUP BY
B.K1,
B.K2,
B.K3,
B.K4
and the 2nd part (Unmatched) it's something like..
INSERT INTO dbo.LSR_Pass
(
K1,
K2,
K3,
K4
)
SELECT
B.K1,
B.K2,
B.K3,
B.K4
FROM dbo.LSR_Prepare B LEFT JOIN dbo.LSR_Pass A ON
A.K1 = B.K1 AND
A.K2 = B.K2 AND
A.K3 = B.K3 AND
A.K4 = B.K4
WHERE
((A.K1 Is Null)
AND
(A.K2 Is Null)
AND
(A.K3 Is Null)
AND
(A.K4 Is Null))
GROUP BY
B.K1,
B.K2,
B.K3,
B.K4
Thanks anyway,
December 17, 2009 at 12:49 pm
Thanks for posting back and updating the forum.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply