March 22, 2010 at 10:55 am
Hi,
I need to INSERT records into my 'Pass' table with records in my 'Prepare' table where the following fields do NOT match:
AB_Id
Priority
Analysis_Code
However, there may be duplicate records in the 'Prepare' table. The default value in the 'Test_Sequence' field ('Pass' table) is set to 1. If there are duplicates, this value needs to increment by 1 for each occurrence.
The Primary Key of the 'Pass' table is: AB_Id, Priority, Analysis_Code, Test_Sequence
The Test_Sequence field does NOT exist in the 'Prepare' table.
INSERT INTO dbo.LSR_Pass
(
AB_Id,
Priority,
ABC,
DEF,
GHI,
Analysis_Code,
Test_Sequence
)
SELECT
A.AB_Id,
A.Priority,
A.ABC,
A.DEF,
A.GHI,
A.Analysis_Code,
MAX(B.Test_Sequence) + 1
FROM dbo.LSR_Prepare A LEFT JOIN dbo.LSR_Pass B ON
A.AB_Id = B.AB_Id AND
A.Priority = B.Priority AND
A.Analysis_Code = B.Analysis_Code
WHERE
((B.AB_Id Is Null)
AND
(B.Priority Is Null)
AND
(B.Analysis_Code Is Null))
The above query generates an error
Msg 2627.
Violation of PRIMARY KEY constraint 'PK_LabSampleResults_Pass'. Cannot insert duplicate key in object 'dbo.LabSampleResults_Pass'.
Any help please?
Thanks in advance,
March 22, 2010 at 11:26 am
Try the following:
with increment as (
SELECT A.AB_Id, A.Priority, A.Analysis_Code
,row_number() over (order by a.ab_id) as Test_Sequence
FROM dbo.LSR_Prepare A
LEFT OUTER JOIN dbo.LSR_Pass B
ON A.AB_Id = B.AB_Id
AND A.Priority = B.Priority
AND A.Analysis_Code = B.Analysis_Code
WHERE ((B.AB_Id Is Null)
AND (B.Priority Is Null)
AND (B.Analysis_Code Is Null))
)
INSERT INTO dbo.LSR_Pass
(
AB_Id,
Priority,
Analysis_Code,
Test_Sequence
)
SELECT A.AB_Id,A.Priority,A.Analysis_Code,MAX(A.Test_Sequence)
FROM dbo.increment A
Group By A.AB_Id,A.Priority,A.Analysis_Code
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
March 22, 2010 at 11:27 am
Below is the psuedo code .... you may have to tweak it to your requirement. For more details, look up CTE in BOL and ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/82fa9016-77db-4b42-b4c8-df6095b81906.htm for Using ROW_NUMBER() with PARTITION.
Below, the code generates a CTE with row numbers for the duplicate rows in increasing sequence. This increasing sequence is limited to the duplicate rows within the partition. I hope this helps you.
;WITH PrepareTestSeq AS
(SELECT *, ROW_NUMBER() OVER(PARTITION_FRAGMENT_ID BY <'Duplicate Value Column in Prepare Table' ORDER BY 'Another Col in Prepare Table'> AS Test_Sequence
FROM tablePrepare)
INSERT INTO dbo.LSR_Pass
(
AB_Id,
Priority,
Analysis_Code,
Test_Sequence
)
SELECT
A.AB_Id,
A.Priority,
A.Analysis_Code,
-- MAX(B.Test_Sequence) + 1
A.Test_Sequence
FROM dbo.PrepareTestSeq A LEFT JOIN dbo.LSR_Pass B ON
A.AB_Id = B.AB_Id AND
A.Priority = B.Priority AND
A.Analysis_Code = B.Analysis_Code
WHERE
((B.AB_Id Is Null)
AND
(B.Priority Is Null)
AND
(B.Analysis_Code Is Null))
March 23, 2010 at 3:46 am
Hi,
Thanks for your help guys... The only thing is the 'Prepare' table does NOT contain a Test_Sequence column. I could include one but I would probably need the set a default to 1 for ALL rows.
The Test_Sequence is used part of the 'Pass' table primary key (PK). It is used to uniquely identify each identical group values of the other 3 PK fields - by increasing the column value by 1 in every event.
Example
PASS Table
Before the INSERT from the Prepare table:
AB_Id, Priority, Analysis_Code, Test_Sequence
1212, 3, 1902, 1
PREPARE Table
The new records to be INSERTED:
AB_Id, Priority, Analysis_Code
1212, 3, 1902
1212, 3, 1902
1212, 3, 1902
PASS Table
and after the update from the Prepare table would be reflected as:
AB_Id, Priority, Analysis_Code, Test_Sequence
1212, 3, 1902, 1
1212, 3, 1902, 2
1212, 3, 1902, 3
1212, 3, 1902, 4
I DO appreciate your assistance,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply