• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Insert into violates primary key

hi there,
I have my table called PATIENTS where the primary key is a combination of DOC_NUM and DOB (FECHA_NACIMIENTO) but on my SELECT insert portion a violation to the primary key ocurrs which is fine. If i execute the statment like it is it fails and nothing gets stored on my table. What i need is for those records that the statement finds a match on my table then just skip it and stored the ones that are not present on my table.
HOw can i do this?
INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT 
ENTIDAD, 
TIPDOC_CAB, 
NUMDOC_CAB, 
TIP_DOC, 
NUM_DOC, 
APELLIDO1, 
APELLIDO2, 
NOMBRE1, 
NOMBRE2, 
FECHA_NACIMIENTO, 
SEXO, 
COD_DEPARTAMENTO, 
COD_MUNICIPIO, 
COD_ZONA
 FROM EPSI03_Regional

Open in new window

0
COHFL
Asked:
COHFL
  • 5
  • 5
2 Solutions
 
Patrick MatthewsCommented:
Assuming ENTIDAD is the primary key...

INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT 
ENTIDAD, 
TIPDOC_CAB, 
NUMDOC_CAB, 
TIP_DOC, 
NUM_DOC, 
APELLIDO1, 
APELLIDO2, 
NOMBRE1, 
NOMBRE2, 
FECHA_NACIMIENTO, 
SEXO, 
COD_DEPARTAMENTO, 
COD_MUNICIPIO, 
COD_ZONA
 FROM EPSI03_Regional e LEFT JOIN
PATIENTS p ON e.[ENTIDAD] = p.[ENTIDAD]
WHERE p.[ENTIDAD] IS NULL

Open in new window

0
 
Patrick MatthewsCommented:
Sorry, wrong primary key :)



INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT 
ENTIDAD, 
TIPDOC_CAB, 
NUMDOC_CAB, 
TIP_DOC, 
NUM_DOC, 
APELLIDO1, 
APELLIDO2, 
NOMBRE1, 
NOMBRE2, 
FECHA_NACIMIENTO, 
SEXO, 
COD_DEPARTAMENTO, 
COD_MUNICIPIO, 
COD_ZONA
 FROM EPSI03_Regional e LEFT JOIN
PATIENTS p ON e.[NUM_DOC] = p.[NUM_DOC] AND e.[FECHA_NACIMIENTO] = p.[FECHA_NACIMIENTO]
WHERE p.[NUM_DOC] IS NULL

Open in new window

0
 
COHFLAuthor Commented:
i have to make a small modification otherwise i will get ambigious errors =)
but im still getting the error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_PATIENTS'. Cannot insert duplicate key in object 'dbo.PATIENTS'.
The statement has been terminated.
INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT 
e.ENTIDAD, 
e.TIPDOC_CAB, 
e.NUMDOC_CAB, 
e.TIP_DOC, 
e.NUM_DOC, 
e.APELLIDO1, 
e.APELLIDO2, 
e.NOMBRE1, 
e.NOMBRE2, 
e.FECHA_NACIMIENTO, 
e.SEXO, 
e.COD_DEPARTAMENTO, 
e.COD_MUNICIPIO, 
e.COD_ZONA
 FROM EPSI03_Regional e LEFT JOIN
PATIENTS p ON e.[NUM_DOC] = p.[NUM_DOC] AND e.[FECHA_NACIMIENTO] = p.[FECHA_NACIMIENTO]
WHERE p.[NUM_DOC] IS NULL

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Patrick MatthewsCommented:
OK, try:


INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT
e.ENTIDAD,
e.TIPDOC_CAB,
e.NUMDOC_CAB,
e.TIP_DOC,
e.NUM_DOC,
e.APELLIDO1,
e.APELLIDO2,
e.NOMBRE1,
e.NOMBRE2,
e.FECHA_NACIMIENTO,
e.SEXO,
e.COD_DEPARTAMENTO,
e.COD_MUNICIPIO,
e.COD_ZONA
 FROM EPSI03_Regional e
WHERE NOT EXISTS
    (SELECT p.[NUM_DOC], p.[FECHA_NACIMIENTO]
    FROM PATIENTS p
    WHERE e.[NUM_DOC] = p.[NUM_DOC] AND e.[FECHA_NACIMIENTO] = p.[FECHA_NACIMIENTO])
0
 
COHFLAuthor Commented:
nope =(
i even try this and it did not worked
BEGIN TRANSACTION 
DECLARE @Any_error int 
DECLARE @SSQL varchar(4000) 
INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT 
e.ENTIDAD, 
e.TIPDOC_CAB, 
e.NUMDOC_CAB, 
e.TIP_DOC, 
e.NUM_DOC, 
e.APELLIDO1, 
e.APELLIDO2, 
e.NOMBRE1, 
e.NOMBRE2, 
e.FECHA_NACIMIENTO, 
e.SEXO, 
e.COD_DEPARTAMENTO, 
e.COD_MUNICIPIO, 
e.COD_ZONA
 FROM EPSI03_Regional e LEFT JOIN
PATIENTS p ON e.[NUM_DOC] = p.[NUM_DOC] AND e.[FECHA_NACIMIENTO] = p.[FECHA_NACIMIENTO]
WHERE p.[NUM_DOC] IS NULL

SELECT @Any_error = @@ERROR   
print @any_error  

IF @Any_error = 2627 --<> 0 AND @Any_error<>
GOTO ErrorHandler      

ErrorHandler:         
	IF @Any_error <> 2627 -- = 0 OR @Any_error=        
		BEGIN             
			PRINT @ssql             
			COMMIT TRAN        
		END        
		ELSE         
		BEGIN             
		PRINT @ssql             
		ROLLBACK TRAN         
	END

Open in new window

0
 
Patrick MatthewsCommented:
Did you try the NOT EXISTS version?  Your script above does not use it.
0
 
COHFLAuthor Commented:
yes i did try the NOT EXISTS but it gives me this:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_PATIENTS'. Cannot insert duplicate key in object 'dbo.PATIENTS'.
The statement has been terminated.
0
 
COHFLAuthor Commented:
this did it
DROP TABLE PATIENTS
CREATE TABLE [dbo].[PATIENTS](
	[ENTIDAD] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TIPDOC_CAB] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NUMDOC_CAB] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TIP_DOC] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NUM_DOC] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[APELLIDO1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[APELLIDO2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NOMBRE1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NOMBRE2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FECHA_NACIMIENTO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SEXO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[COD_DEPARTAMENTO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[COD_MUNICIPIO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[COD_ZONA] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName]
ON [dbo].[PATIENTS]
(
[NUM_DOC] ASC,
[FECHA_NACIMIENTO] ASC
) WITH (IGNORE_DUP_KEY = ON)ON [PRIMARY]
GO

INSERT INTO PATIENTS
           ([ENTIDAD]
           ,[TIPDOC_CAB]
           ,[NUMDOC_CAB]
           ,[TIP_DOC]
           ,[NUM_DOC]
           ,[APELLIDO1]
           ,[APELLIDO2]
           ,[NOMBRE1]
           ,[NOMBRE2]
           ,[FECHA_NACIMIENTO]
           ,[SEXO]
           ,[COD_DEPARTAMENTO]
           ,[COD_MUNICIPIO]
           ,[COD_ZONA])
   
SELECT 
e.ENTIDAD, 
e.TIPDOC_CAB, 
e.NUMDOC_CAB, 
e.TIP_DOC, 
e.NUM_DOC, 
e.APELLIDO1, 
e.APELLIDO2, 
e.NOMBRE1, 
e.NOMBRE2, 
e.FECHA_NACIMIENTO, 
e.SEXO, 
e.COD_DEPARTAMENTO, 
e.COD_MUNICIPIO, 
e.COD_ZONA
 FROM EPSI03_Regional e 
WHERE NOT EXISTS
    (SELECT p.[NUM_DOC], p.[FECHA_NACIMIENTO]
    FROM PATIENTS p
    WHERE e.[NUM_DOC] = p.[NUM_DOC] AND e.[FECHA_NACIMIENTO] = p.[FECHA_NACIMIENTO])

Open in new window

0
 
Patrick MatthewsCommented:
The code the Asker indicated s/he is using uses the same INSERT statement from my comment http:#a35507196
0
 
COHFLAuthor Commented:
i just used but it is irrelevant to be honest the table gets drop on the first line therefore the table is empty to beging with. even if i run the original statement works.
0
 
Alpesh PatelAssistant ConsultantCommented:
Please make sure primary key value must be unique and not null.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now