wayne7215 wayne7215 - 18 days ago 7
SQL Question

Insert where not exists Violation of PRIMARY KEY

I'm having troubles with an Insert where not exists and I'm not sure if a MERGE statement would be more efficient or what's wrong with my statement.

I have en existing View and need to insert the new records of this View into a Table.

The Table looks like:

CREATE TABLE [dbo].[ser_number_all]
(Serialnumber nvarchar(100) PRIMARY KEY,
TypeName nvarchar(max),
Date datetime,
Parent_Serialnumber nvarchar(100),
JobNumber nvarchar(30),
ProductNode hierarchyid,
);


The Insert statement looks like this:

insert into [dbo].[ser_number_all]
( Serialnumber
, TypeName
, Date
, Parent_Serialnumber
, JobNumber
, ProductNode)
select Serialnumber
, TypeName
, Date
, Parent_Serialnumber
, JobNumber
, ProductNode
from dbo.Hierachical_View_with_Jobnumbers as ser_number_all
where not exists (select 1
from Hierachical_View_with_Jobnumbers as hv
where hv. Serialnumber = ser_number_all.Serialnumber
and hv. TypeName = ser_number_all.TypeName
and hv. Date = ser_number_all.Date
and hv. Parent_Serialnumber = ser_number_all.Parent_Serialnumber
and hv. JobNumber = ser_number_all.JobNumber
and hv. ProductNode = ser_number_all.ProductNode);


As long the View has not any new records, it looks ok and I'm not getting any error, the output is 0 records as it should be.

When I add a new record to the origin table and the view has 1 record more, I'm always getting this error:

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK__ser_numb__F2753A12C4ABA976'. Cannot insert duplicate key in object 'dbo.ser_number_all'. The duplicate key value is (.x3666AB05).
The statement has been terminated.


I don't get it why it will insert a duplicate value in the primary key column because in my WHERE clause I can't see any mistake.

I have also tried with IS NULL instead = ser_number_all.TypeName and for all other columns where it could have a NULL value, but still the same.

Again, I'm coming from Oracle and it looks like I have to learn many diversities with MS SQL compared to Oracle.

Appreciate any suggestion :-)
Thx

EDIT:

Here the code of the View:

CREATE VIEW [dbo].[Hierachical_View_with_Jobnumbers]
AS
WITH ProductList
AS
(
SELECT p.Serialnumber,
p.Type_Id,
p.Parent_Serialnumber,
p.ActiveJob_Jobnumber as JobNumber,
N'/' + CONVERT(NVARCHAR(4000), ROW_NUMBER() OVER (ORDER BY p.Serialnumber)) + N'/' AS ProductNode_AsChar
FROM Products AS p
WHERE p.Parent_Serialnumber IS NULL

UNION ALL

SELECT p.Serialnumber,
p.Type_Id,
p.Parent_Serialnumber,
JobNumber,
pl.ProductNode_AsChar + CONVERT(NVARCHAR(4000), ROW_NUMBER() OVER (ORDER BY p.Serialnumber)) + N'/'
FROM Products AS p
INNER JOIN ProductList AS pl ON p.Parent_Serialnumber = pl.Serialnumber
)
SELECT Serialnumber,
pt.Name as TypeName,
Parent_Serialnumber,
JobNumber,
CONVERT(HIERARCHYID, ProductNode_AsChar) AS ProductNode
FROM ProductList as pl
INNER JOIN ProductTypes as pt on pl.Type_Id = pt.Id;

Answer

@TheGameiswar

Sorry, now I got it what you meant ;-) Stupid me...

Here the solution which works now with correctly correlating:

    insert into [dbo].[ser_number_all]
  ( Serialnumber
  , TypeName
  , Date
  , Parent_Serialnumber
  , JobNumber
  , ProductNode)
  select    Serialnumber
          , TypeName
          , Date
          , Parent_Serialnumber
          , JobNumber
          , ProductNode
  from dbo.Hierachical_View_with_Jobnumbers as hv
where not exists (select 1
                  from ser_number_all as sna
                  where hv. Serialnumber = sna.Serialnumber);

Thank you all for your time and guiding me to the right direction :-)

Comments