I'm trying to insert data in to a table called product structure:
insert into [Product_Structure] (PARPRT_02,COMPRT_02, EFFDTE_02, QTYPER_02,QTYCDE_02,LTOSET_02,TYPCDE_02,SCRAP_02,ECN_02,ALTPRT_02,REFDES_02,MPNSTR_02,MCOMP_02,MSITE_02,UDFKEY_02,UDFREF_02,CreatedBy,CreationDate,ModifiedBy,ModificationDate,ALTCDE_02) select PARPRT_02,'349-00006001-03','2018-10-10','0.125', 'U','' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'ONYX' ,'2018-10-10','ONYX' ,'2018-10-10','' FROM [Product_Structure] WHERE PARPRT_02 LIKE '41%
The query gives me an error:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Product_Structure' with unique index 'key0'. The duplicate key value is (410-03812681-01 , 349-00006001-03 , Oct 10 2018 12:00AM, ).
The statement has been terminated.
It seems to stem from the bigint data type:
[MAXID] [bigint] IDENTITY(100000,1) NOT NULL
To test I created Product_Structuretest table using a decimal data type:
[MAXID] [decimal](18, 0) IDENTITY(100000,1) NOT NULL,
and the query works.
My question is there any reason why I shouldn't use decimal instead of bigint?
The DB is part for our ERP software and I'm looking for 3rd party opinions before going into battle.
btw, if there is another way of achieving the insert while using the BigInt datatype, I would rather use that and not have to deal with the customer support.