Primary Key Violation, duplication

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Primary Key Violation, duplication

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2010-08-20 : 20:52:54

I'm getting a primary key error with the code below. SQL analyzer says I cannot duplicate values. The purpose of this code is to take a vendor and make a copy of it except with an 'R' in front of the original vendor number. The thing is I have already accounted for this, I created a temp table called @vendorcopy1 with just the R vendors in it, and I compare against this in the final WHERE clause. I think this is a problem with the database, because this query runs successfully against our test database but not the live database. There are no identity columns on this table, just a PK, why am I getting this error?


DECLARE @vendorcopy1 TABLE
(vendnum VendNumType)
 
DECLARE @donotuse TABLE
(vendnum VendNumType)
 
--Insert DO NOT USE vendors into this table, later this table will be referenced so as not to build a
--remit to record for vendor numbers that are not used.
INSERT INTO @donotuse
SELECT
vend_num
FROM vendaddr
WHERE
name LIKE 'DO NOT%'
 
--Insert all of the current remit to records into this table.  This is used so it won't try to create
--a remit to record for a vendor that already has one.
INSERT INTO @vendorcopy1
SELECT
vend_num
FROM
vendor
WHERE
vendor.vend_num LIKE 'R%'
--------------------------------------------------------------------------------------------------------
--This step makes a copy of every vendor record and appends the vendor number with an R, also sets
--Show in Drop Down Lists to 0 (no)
INSERT INTO [GVC_Pilot].[dbo].[vendor]
           ([vend_num]
           ,[contact]
           ,[phone]
           ,[vend_type]
           ,[terms_code]
           ,[ship_code]
           ,[fob]
           ,[print_price]
           ,[stat]
           ,[vend_remit]
           ,[whse]
           ,[charfld1]
           ,[charfld2]
           ,[charfld3]
           ,[decifld1]
           ,[decifld2]
           ,[decifld3]
           ,[logifld]
           ,[datefld]
           ,[curr_code]
           ,[tax_reg_num1]
           ,[bank_code]
           ,[pay_type]
           ,[pay_lst_yr]
           ,[edi_vend]
           ,[branch_id]
           ,[trans_nat]
           ,[delterm]
           ,[process_ind]
           ,[tax_reg_num2]
           ,[tax_code1]
           ,[tax_code2]
           ,[lang_code]
           ,[pur_acct]
           ,[pur_acct_unit1]
           ,[pur_acct_unit2]
           ,[pur_acct_unit3]
           ,[pur_acct_unit4]
           ,[lcr_reqd]
           ,[category]
           ,[account]
           ,[account_name]
           ,[EFT_bank_num]
           ,[print_vat_on_po]
           ,[price_by]
           ,[include_tax_in_cost]
           ,[trans_nat_2]
           ,[active_for_data_integration]
           ,[supply_web_vendor]
           ,[request_acknowledgement]
           ,[vch_over_po_cost_tolerance]
           ,[vch_under_po_cost_tolerance]
           ,[pay_fiscal_ytd]
           ,[pay_lst_fiscal_yr]
           ,[transit]
           ,[synchronized_to_bus]
           ,[show_in_drop_down_list])
     SELECT
                  CASE
                        WHEN LEN(LTRIM(VENDOR.VEND_NUM))=5THEN 'R0'+ LTRIM(vendor.vend_num)
                        WHEN LEN(LTRIM(VENDOR.VEND_NUM))=6THEN 'R'+ LTRIM(vendor.vend_num)
                  END
           ,contact
           ,phone
           ,vend_type
           ,terms_code
           ,ship_code
           ,fob
           ,print_price
           ,stat
           ,vend_remit
           ,whse
           ,charfld1
           ,charfld2
           ,charfld3
           ,decifld1
           ,decifld2
           ,decifld3
           ,logifld
           ,datefld
           ,curr_code
           ,tax_reg_num1
           ,bank_code
           ,pay_type
           ,pay_lst_yr
           ,edi_vend
           ,branch_id
           ,trans_nat
           ,delterm
           ,process_ind
           ,tax_reg_num2
           ,tax_code1
           ,tax_code2
           ,lang_code
           ,pur_acct
           ,pur_acct_unit1
           ,pur_acct_unit2
           ,pur_acct_unit3
           ,pur_acct_unit4
           ,lcr_reqd
           ,category
           ,account
           ,account_name
           ,EFT_bank_num
           ,print_vat_on_po
           ,price_by
           ,include_tax_in_cost
           ,trans_nat_2
           ,active_for_data_integration
           ,supply_web_vendor
           ,request_acknowledgement
           ,vch_over_po_cost_tolerance
           ,vch_under_po_cost_tolerance
           ,pay_fiscal_ytd
           ,pay_lst_fiscal_yr
           ,transit
           ,synchronized_to_bus
           ,0
FROM vendor
WHERE
vend_remit is null AND
vend_num NOT LIKE 'R%' AND
NOT EXISTS (SELECT * FROM @vendorcopy1 vc1
                        WHERE vc1.vendnum =
                              CASE
                                    WHENLEN(LTRIM(VENDOR.VEND_NUM))=5 THEN 'R0'+LTRIM(vendor.vend_num)
                                    WHENLEN(LTRIM(VENDOR.VEND_NUM))=6 THEN 'R'+ LTRIM(vendor.vend_num)
                              END)
AND
vend_num NOT IN (SELECT vendnum FROM @donotuse)

Kristen
Test

22859 Posts

Posted - 2010-08-21 : 04:02:25

I would do it the other way about. Create a temporary table with existing Vendor Number and the New Vendor Number (with the "R" or "R0" prefix). Only put into this table rows you want to insert. If necessary after INSERT then do some DELETEs (or UPDATEs even) in the temporary table to refine it to the correct rows.

Then you can SELECT from that table to check it is all correct. Important;y you can also check that there are NO duplicates on the New Vendor Number [within the Temporary table] and no duplicates between New Vendor Number, in temporary table, and Vendor Number in Vendor table.

Then INSERT using a JOIN between temporary table and Vendor table.

The way that you are using is very hard to debug because it all happens in the final INSERT - so when that goes Bang! you can't just do

SELECT NewVendorNumber
FROM @MyTempTable
GROUP BY NewVendorNumber
HAVING COUNT(*) > 1

or

SELECT NewVendorNumber
FROM @MyTempTable
WHERE EXISTS (SELECT * FROM Vendor WHERE vendnum = NewVendorNumber)

mmccardle
Starting Member

43 Posts

Posted - 2010-08-23 : 10:37:12

Thanks for the help. I revised the code to your suggestion and it ran a lot faster. My problem was that I had named the configuration in one of the insert statements, when I changed the configuration in SQL Management studio from Pilot to Live, it was still trying to pickup the Pilot configuration where I had told it to.

Here is the new code.


DECLARE @vendorcopy1 TABLE
(vend_num nvarchar (7),
contact nvarchar (30),
phone nvarchar (25),
vend_type nvarchar (6),
terms_code nvarchar (3),
ship_code nvarchar (4),
fob nvarchar (60),
print_price tinyint,
stat nchar (1),
vend_remit nvarchar (7),
whse nvarchar (4),
charfld1 nvarchar (20),
charfld2 nvarchar (20),
charfld3 nvarchar (20),
decifld1 decimal,
decifld2 decimal,
decifld3 decimal,
logifld tinyint,
datefld datetime,
curr_code nvarchar (3),
tax_reg_num1 nvarchar (25),
bank_code nvarchar (3),
pay_type nchar (1),
pay_lst_yr decimal,
edi_vend tinyint,
branch_id nvarchar (3),
trans_nat nvarchar (2),
delterm nvarchar (4),
process_ind nvarchar (1),
tax_reg_num2 nvarchar (25),
tax_code1 nvarchar (6),
tax_code2 nvarchar (6),
lang_code nvarchar (3),
pur_acct nvarchar (12),
pur_acct_unit1 nvarchar (4),
pur_acct_unit2 nvarchar (4),
pur_acct_unit3 nvarchar (4),
pur_acct_unit4 nvarchar (4),
lcr_reqd tinyint,
category nvarchar (6),
account nvarchar (20),
account_name nvarchar (32),
EFT_bank_num nvarchar (6),
print_vat_on_po tinyint,
price_by nchar (1),
include_tax_in_cost tinyint,
trans_nat_2 nvarchar (2),
active_for_data_integration tinyint,
supply_web_vendor tinyint,
request_acknowledgement tinyint,
vch_over_po_cost_tolerance decimal,
vch_under_po_cost_tolerance decimal,
pay_fiscal_ytd decimal,
pay_lst_fiscal_yr decimal,
transit int,
synchronized_to_bus tinyint,
show_in_drop_down_list tinyint)



--Insert all of the current remit to records into this table.  This is used so it won't try to create
--a remit to record for a vendor that already has one.
INSERT INTO @vendorcopy1
SELECT
			CASE
				WHEN LEN(LTRIM(VENDOR.VEND_NUM))=5 THEN 'R0'+ LTRIM(vendor.vend_num)
				WHEN LEN(LTRIM(VENDOR.VEND_NUM))=6 THEN 'R'+ LTRIM(vendor.vend_num)
			END
           ,[contact]
           ,[phone]
           ,[vend_type]
           ,[terms_code]
           ,[ship_code]
           ,[fob]
           ,[print_price]
           ,[stat]
           ,[vend_remit]
           ,[whse]
           ,[charfld1]
           ,[charfld2]
           ,[charfld3]
           ,[decifld1]
           ,[decifld2]
           ,[decifld3]
           ,[logifld]
           ,[datefld]
           ,[curr_code]
           ,[tax_reg_num1]
           ,[bank_code]
           ,[pay_type]
           ,[pay_lst_yr]
           ,[edi_vend]
           ,[branch_id]
           ,[trans_nat]
           ,[delterm]
           ,[process_ind]
           ,[tax_reg_num2]
           ,[tax_code1]
           ,[tax_code2]
           ,[lang_code]
           ,[pur_acct]
           ,[pur_acct_unit1]
           ,[pur_acct_unit2]
           ,[pur_acct_unit3]
           ,[pur_acct_unit4]
           ,[lcr_reqd]
           ,[category]
           ,[account]
           ,[account_name]
           ,[EFT_bank_num]
           ,[print_vat_on_po]
           ,[price_by]
           ,[include_tax_in_cost]
           ,[trans_nat_2]
           ,[active_for_data_integration]
           ,[supply_web_vendor]
           ,[request_acknowledgement]
           ,[vch_over_po_cost_tolerance]
           ,[vch_under_po_cost_tolerance]
           ,[pay_fiscal_ytd]
           ,[pay_lst_fiscal_yr]
           ,[transit]
           ,[synchronized_to_bus]
           ,0
FROM
vendor INNER JOIN vendaddr on vendor.vend_num=vendaddr.vend_num
WHERE
vendor.vend_num NOT LIKE 'R%' AND
vendaddr.name NOT LIKE 'DO NOT%'

DELETE FROM @vendorcopy1 WHERE vend_num IN (select vend_num FROM vendor)

--SELECT * FROM @vendorcopy1
--------------------------------------------------------------------------------------------------------
--This step makes a copy of every vendor record and appends the vendor number with an R, also sets
--Show in Drop Down Lists to 0 (no)
INSERT INTO vendor
           ([vend_num]
           ,[contact]
           ,[phone]
           ,[vend_type]
           ,[terms_code]
           ,[ship_code]
           ,[fob]
           ,[print_price]
           ,[stat]
           ,[vend_remit]
           ,[whse]
           ,[charfld1]
           ,[charfld2]
           ,[charfld3]
           ,[decifld1]
           ,[decifld2]
           ,[decifld3]
           ,[logifld]
           ,[datefld]
           ,[curr_code]
           ,[tax_reg_num1]
           ,[bank_code]
           ,[pay_type]
           ,[pay_lst_yr]
           ,[edi_vend]
           ,[branch_id]
           ,[trans_nat]
           ,[delterm]
           ,[process_ind]
           ,[tax_reg_num2]
           ,[tax_code1]
           ,[tax_code2]
           ,[lang_code]
           ,[pur_acct]
           ,[pur_acct_unit1]
           ,[pur_acct_unit2]
           ,[pur_acct_unit3]
           ,[pur_acct_unit4]
           ,[lcr_reqd]
           ,[category]
           ,[account]
           ,[account_name]
           ,[EFT_bank_num]
           ,[print_vat_on_po]
           ,[price_by]
           ,[include_tax_in_cost]
           ,[trans_nat_2]
           ,[active_for_data_integration]
           ,[supply_web_vendor]
           ,[request_acknowledgement]
           ,[vch_over_po_cost_tolerance]
           ,[vch_under_po_cost_tolerance]
           ,[pay_fiscal_ytd]
           ,[pay_lst_fiscal_yr]
           ,[transit]
           ,[synchronized_to_bus]
           ,[show_in_drop_down_list])
     SELECT *
FROM @vendorcopy1 vc1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources