Trying to insert unique columns into table

[rminnich85]

1. I am creating a table which contains all of the Vendor IDs in the following statement:

select * into edi_temp from temp_po where VendorID in (001150,
010134,
010306,
010601,
010638,
010769,
010770,
011105,
011148,
011197,
013297,
013300,
013606,
014019,
014045,
018456,
018622,
019364,
020199,
020305,
020609,
021200,
021434,
021455,
021632,
030030,
031120,
031260,
031742,
031996,
031997,
032120,
032228,
032433,
032449,
036585,
040212,
050105,
050202,
051332,
060467,
060518,
060580,
071307,
071381,
071423,
080315,
080747,
080749,
080838,
082131,
082200,
090134,
090252,
090331,
091062,
108169,
120505,
120675,
127572,
131699,
132498,
132506,
132795,
132903,
133371,
133422,
134942,
141242,
150521,
150600,
153909,
160242,
160297,
160775,
161160,
161844,
162552,
163030,
163222,
163223,
182105,
190163,
191601,
192240,
192400,
192469,
192919,
193048,
193049,
193508,
193510,
194027,
194158,
194209,
194407,
194431,
194949,
196485,
200220,
200570,
201608,
217496,
218360,
220100,
230470,
261298,
280395,
280985,
281554,
281582,
281597,
281695,
281736,
282023,
282130,
330110,
330264,
330971,
331012,
331202,
331211,
346716)

2. I then try to insert the Distinct rows from the edi_temp table into B2520400. However, I am getting a primary key violation if I have more than one row in the table. Here is the error:

Violation of PRIMARY KEY constraint 'PKB2520400'. Cannot insert duplicate key in object 'B2520400'.
The statement has been terminated.


INSERT INTO B2520400
(a.PONUMBER, a.BS25_Department_ID,a.BS25_PO_Prefix, a.LOCNCODE )


SELECT DISTINCT b.UpdatedPO, substring(b.distribution,1,3) as BS25_Department_ID, b.PONumber, b.SiteID
FROM
edi_temp AS b

WHERE NOT EXISTS
(

SELECT 1
FROM B2520400 AS a
WHERE a.PONUMBER = b.UpdatedPO
AND a.BS25_Department_ID = substring(b.distribution,1,3)
AND a.BS25_PO_Prefix = b.PONumber
AND a.LOCNCODE=b.SiteID



)

 

[gk53]

modify your statement as

INSERT INTO B2520400
(a.PONUMBER, a.BS25_Department_ID,a.BS25_PO_Prefix, a.LOCNCODE )
SELECT DISTINCT b.UpdatedPO, substring(b.distribution,1,3) as BS25_Department_ID, 
			b.PONumber, b.SiteID
FROM edi_temp AS b
left join B2520400 a
	on a.PONUMBER = b.UpdatedPO
		AND a.BS25_Department_ID = substring(b.distribution,1,3)
		AND a.BS25_PO_Prefix = b.PONumber
		AND a.LOCNCODE=b.SiteID
WHERE a.PONUMBER is null

it should work