Violation of PRIMARY KEY constraint

I'm running the below script but receive a 'Violation of PRIMARY KEY constraint' error.

The primary key is SKU - CHAR(15)

Any suggestions?
insert into sku_master
select 
	SKU = convert(char(15),I.ITEMNMBR),
	Class = '01',
	Env_Code = ' ',
	Storage_Velocity_Code = ' ',
	CC_Velocity_Code = ' ',
	Description1 = convert(char(50),I.ITEMDESC),
	Description2 = ' ',
	Unit_Desc = ' ',
	Bulk_Desc = ' ',
	Bulk_Qty = 0,
	Bulk_Pick_Flag = 'N',
	Bulk_Loc_Type = ' ',
	Unit_Loc_Type = ' ',
	Length = 0,
	Width = 0,
	Height = 0,
	Cube = 0,
	Weight = convert(float,I.ITEMSHWT),
	Serial_Track = 'N',
	Lot_Track = 'N',
	Exp_Date_Track = 'N',
	Mfg_Date_Track = 'N',
	High_Qty = 0,
	Tie_Qty = 0,
	Preemptive_Putaway_Flag = 'N',
	Shippable_Unit = 'N',
	Cost = convert(float,I.CURRCOST),
	Substitute_SKU = convert(char(15),I.ALTITEM1),
	New_SKU = 'N',
	Age_Control = 'N',
	Rule_ID = 'DEFLT',
	Preferred_Zone = ' ',
	PickGen_Rule = 'RULE1',
	Update_Date = getdate(),
	Update_User_ID = 'INIT',
	Update_PID = 'DTS'
	from GP9TST.KELEG.dbo.IV00101 I
	JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
	select SKU from sku_master) and N.LOCNCODE = 'MEMPHIS'

Open in new window

jdr0606Asked:
Who is Participating?
 
60MXGConnect With a Mentor Commented:
insert into sku_master
select
      SKU = distinct(convert(char(15),I.ITEMNMBR)),
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(char(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(char(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select distinct(SKU) from sku_master) and N.LOCNCODE = 'MEMPHIS'  
0
 
60MXGCommented:
I believed that the query written here

select
      distinct(SKU = convert(char(15),I.ITEMNMBR)),  --- Added distinct here
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(char(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(char(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select SKU from sku_master) and N.LOCNCODE = 'MEMPHIS'
 

----has duplicated primary key so the sku_master table is rejecting it.  You might want to change the query like this

select
      SKU = convert(char(15),I.ITEMNMBR),
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(char(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(char(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select SKU from sku_master) and N.LOCNCODE = 'MEMPHIS'
0
 
jdr0606Author Commented:
Unfortunately, I still received a Primary Key violation error using the modified script.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
60MXGCommented:
Exam this script

select
      SKU = convert(char(15),I.ITEMNMBR),
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(char(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(char(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select SKU from sku_master) and N.LOCNCODE = 'MEMPHIS'
0
 
60MXGCommented:
Can you give me the sku_master table structure.  Something like this

sku_master_id                  [int]    PrimaryKey,
sku_master_desc              varchar(100)
0
 
60MXGCommented:
also try to change your datatype from char to varchar unless all the data are fix length.  
0
 
jdr0606Author Commented:
Does this code snippet help?


CREATE TABLE [dbo].[SKU_Master](
	[SKU] [dbo].[SKU] NOT NULL,
	[Class] [dbo].[Class] NOT NULL,
	[Env_Code] [dbo].[Env_Code] NOT NULL,
	[Storage_Velocity_Code] [dbo].[Velocity_Code] NOT NULL,
	[CC_Velocity_Code] [dbo].[Velocity_Code] NOT NULL,
	[Description1] [dbo].[SKU_Desc] NOT NULL,
	[Description2] [dbo].[SKU_Desc] NOT NULL,
	[Unit_Desc] [dbo].[UoM] NOT NULL,
	[Bulk_Desc] [dbo].[UoM] NOT NULL,
	[Bulk_Qty] [dbo].[Qty] NOT NULL,
	[Bulk_Pick_Flag] [dbo].[Boolean] NOT NULL,
	[Bulk_Loc_Type] [dbo].[Loc_Type] NOT NULL,
	[Unit_Loc_Type] [dbo].[Loc_Type] NOT NULL,
	[Length] [dbo].[Dimension] NOT NULL,
	[Width] [dbo].[Dimension] NOT NULL,
	[Height] [dbo].[Dimension] NOT NULL,
	[Cube] [dbo].[Dimension] NOT NULL,
	[Weight] [dbo].[Dimension] NOT NULL,
	[Serial_Track] [dbo].[Boolean] NOT NULL,
	[Lot_Track] [dbo].[Boolean] NOT NULL,
	[Exp_Date_Track] [dbo].[Boolean] NOT NULL,
	[Mfg_Date_Track] [dbo].[Boolean] NOT NULL,
	[High_Qty] [dbo].[Qty] NOT NULL,
	[Tie_Qty] [dbo].[Qty] NOT NULL,
	[Preemptive_Putaway_Flag] [dbo].[Boolean] NOT NULL,
	[Shippable_Unit] [dbo].[Boolean] NOT NULL,
	[Cost] [float] NOT NULL,
	[Substitute_SKU] [dbo].[SKU] NOT NULL,
	[New_SKU] [dbo].[Boolean] NOT NULL,
	[Age_Control] [dbo].[Boolean] NOT NULL,
	[Rule_ID] [dbo].[Small_ID_String] NOT NULL,
	[Preferred_Zone] [dbo].[Zone] NOT NULL,
	[PickGen_Rule] [dbo].[Small_ID_String] NOT NULL,
	[Update_Date] [datetime] NOT NULL,
	[Update_User_ID] [dbo].[User_ID] NOT NULL,
	[Update_PID] [dbo].[PID] NULL,
 CONSTRAINT [PK_SKU_Master_1__12] PRIMARY KEY CLUSTERED 
(
	[SKU] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

0
 
60MXGCommented:
Try to remove the Primary Key on the SKU column in your SKU_Master table if you just want to import the data.  Your datasource has duplicated Sku I believed so exam the output of your select statement.

select
      distinct(SKU = convert(varchar(15),I.ITEMNMBR)),       --- added distinct and change datatype
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(varchar(50),I.ITEMDESC),            -- change datatype
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(varchar(15),I.ALTITEM1),            --- change data type
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNM
0
 
jdr0606Author Commented:
It cutoff the last line or two, was there any changes on them?

Thanks
0
 
60MXGCommented:
I think I find it

select
      distinct(SKU = convert(varchar(15),I.ITEMNMBR)),   ---added
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(varchar(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(varchar(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select distinct(SKU) from sku_master) and N.LOCNCODE = 'MEMPHIS'   ----added distinct
0
 
60MXGCommented:
sorry I cut off the line when I copy and paste
0
 
jdr0606Author Commented:
Getting the following on the code

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'and'.
0
 
60MXGCommented:
select distinct(SKU = convert(varchar(15),I.ITEMNMBR)),   --this line of code could be wrong then try this

select (distinct(SKU) = convert(varchar(15),I.ITEMNMBR)),  
0
 
60MXGCommented:
Get it to work?
0
 
jdr0606Author Commented:
Still getting syntax errors.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'and'.

select 
      select (distinct(SKU) = convert(varchar(15),I.ITEMNMBR)),  
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(varchar(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(varchar(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select distinct(SKU) from sku_master) and N.LOCNCODE = 'MEMPHIS'

Open in new window

0
 
60MXGCommented:
select
      select (distinct(SKU)) = convert(varchar(15),I.ITEMNMBR),  
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(varchar(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(varchar(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select distinct(SKU) from sku_master) and N.LOCNCODE = 'MEMPHIS'
0
 
jdr0606Author Commented:
What am I doing wrong?  I still get the below errors using your latest query syntax.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'and'.
0
 
60MXGCommented:
insert into sku_master
select
      SKU = convert(char(15),I.ITEMNMBR),
      Class = '01',
      Env_Code = ' ',
      Storage_Velocity_Code = ' ',
      CC_Velocity_Code = ' ',
      Description1 = convert(char(50),I.ITEMDESC),
      Description2 = ' ',
      Unit_Desc = ' ',
      Bulk_Desc = ' ',
      Bulk_Qty = 0,
      Bulk_Pick_Flag = 'N',
      Bulk_Loc_Type = ' ',
      Unit_Loc_Type = ' ',
      Length = 0,
      Width = 0,
      Height = 0,
      Cube = 0,
      Weight = convert(float,I.ITEMSHWT),
      Serial_Track = 'N',
      Lot_Track = 'N',
      Exp_Date_Track = 'N',
      Mfg_Date_Track = 'N',
      High_Qty = 0,
      Tie_Qty = 0,
      Preemptive_Putaway_Flag = 'N',
      Shippable_Unit = 'N',
      Cost = convert(float,I.CURRCOST),
      Substitute_SKU = convert(char(15),I.ALTITEM1),
      New_SKU = 'N',
      Age_Control = 'N',
      Rule_ID = 'DEFLT',
      Preferred_Zone = ' ',
      PickGen_Rule = 'RULE1',
      Update_Date = getdate(),
      Update_User_ID = 'INIT',
      Update_PID = 'DTS'
      from GP9TST.KELEG.dbo.IV00101 I
      JOIN GP9TST.KELEG.dbo.IV00102 N on I.ITEMNMBR = N.ITEMNMBR where convert(char(15),I.ITEMNMBR) not in (
      select distinct(SKU) from sku_master) and N.LOCNCODE = 'MEMPHIS'    -- added distinct
 
--- try your old query here
0
 
jdr0606Author Commented:
Ran the above and received the Primary key error again
0
 
jdr0606Author Commented:
Still generating error.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'distinct'.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'and'.
0
 
jdr0606Author Commented:
It turns out that the test DB I was pointing to had an item number greater than 15 characters but when it was converted down to 15 characters ended up being a duplicate and created the primary key error.

Still dont' know why I kept getting the syntax errors but the real problem has been resolved.

Thanks again for your assistance!
0
 
60MXGCommented:
use Varchar instead of Char.  I read some article about varchar and char.  Varchar is much better in term of performance and has less problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.