Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jayram11
Yak Posting Veteran
97 Posts |
 Posted - 2010-09-24 : 16:49:54
|
| I have DRUG_DOSAGE (DRUG_HCPCS, DRUG_DOSAGE) and DRUGSNDC (DRUGNDC_HCPCS, DOSAGE ) tables with 2 variables in each. I want to update DRUG_DOSAGE with records from DRUGSNDC which are not in DRUG_DOSAGE. DRUGSNDC has duplicate values and DRUG_DOSAGE has unique values (PK DRUG_HCPCS)I tried these 2 methods and bot say PK violationmethod1insert into DRUG_DOSAGE (DRUG_HCPCS, DRUG_DOSAGE)select distinct(DRUGNDC_HCPCS), DOSAGEfrom DRUGSNDCwhere DRUGNDC_HCPCS not in (select drug_hcpcs from DRUG_dosage)and DRUGNDC_HCPCS <> 'N/A' method 2INSERT INTO DRUG_DOSAGE (DRUG_HCPCS, DRUG_DOSAGE)SELECT DRUGNDC_HCPCS, DOSAGE FROM DRUGSNDCWHERE NOT EXISTS(SELECT * FROM DRUG_DOSAGE WHERE (DRUGSNDC.DRUGNDC_HCPCS = DRUG_DOSAGE.DRUG_HCPCS and DRUGSNDC.DOSAGE = DRUG_DOSAGE.DRUG_DOSAGE) ) AND DRUGNDC_HCPCS <> 'N/A'What am i doing wrong?thaks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jayram11
Yak Posting Veteran
97 Posts |
Posted - 2010-09-24 : 17:46:34
|
| I dont see adiffernece between my query and urs but i still get the errorViolation of PRIMARY KEY constraint 'PK_DRUG_DOSAGE'. Cannot insert duplicate key in object 'DRUG_DOSAGE'.The statement has been terminated.(0 row(s) affected) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Inserting Unique values from one row to another