Big Int Problem

Onyxtacular · October 18, 2018, 3:44pm

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.

Thanks,
Onyx

harishgg1 · October 18, 2018, 4:31pm

The way i read it

There is unique index on column
And you are inserting duplicate
Into the column

Unique index means no duplicate can be inserted

Why it's treating as duplicate
There can be several reasons

Onyxtacular · October 18, 2018, 4:38pm

I get that error every time even if I

 delete  [Product_Structure]
 where PARPRT_02 like '41%'

before the query.

Thanks for taking the time to respond.

harishgg1 · October 18, 2018, 4:40pm

There could be several reasons

How do you find out what is going on ?
Is the key

Maybe Scott Pletcher could help us on this
Sorry Scott if i offend you
I mean by asking you here

yosiasz · October 18, 2018, 4:52pm

how many rows do you get when you do

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%

post results here

harishgg1 · October 18, 2018, 5:01pm

What if this returns only one row
I know about select ing

What other ways are there?
Curious. Please let me know
Yosiasz

Onyxtacular · October 18, 2018, 5:09pm

on the test db I get

 (5929304 row(s) affected)

harishgg1 · October 18, 2018, 5:25pm

If possible
Do

Group by
Having count(*) > 1

To get duplicate s

Onyxtacular · October 18, 2018, 5:37pm

There are no duplicates. as a part of testing I deleted all the 41%.

ScottPletcher · October 18, 2018, 6:21pm

SQL is showing you a key that is a duplicate; the index named "key0" has (at least) 3 columns in it, which have the values:
(410-03812681-01 , 349-00006001-03 , Oct 10 2018 12:00AM, )

yosiasz · October 18, 2018, 7:21pm

is that how many rows you would like to add to Product_structure?

also do this query and post back what you get?

select count(1), PARPRT_02
from Product_Structure
group by PARPRT_02
having count(1) > 1

Onyxtacular · October 19, 2018, 11:31am

I would say that is a valid point but the query works fine if the data type of MAXID is changed from BIGINT to DECIMAL.

when I run

  select count(*) from Product_Structure WHERE PARPRT_02 LIKE '41%'

it returns: 1496282

returned

(317815 row(s) affected)

Here's a sample:
36 0631-0021-0001
3 1500-9009-0003
3 1590-0330-0003
3 1590-0336-0003
3 1590-0342-0003
3 1590-0384-0003
3 1590-0396-0003
2 1590-1000-1002
3 1591-0330-0003
3 1591-0336-0003
3 1591-0342-0003
3 1591-0384-0003
3 1591-0396-0003
3 1592-0336-0003
3 1592-0384-0003
3 1592-0396-0003
3 1593-0330-0003
3 1593-0336-0003

harishgg1 · October 19, 2018, 12:01pm

the reason why it works when changed from bigint to decimal

COULD BE

Lets says we have
table1
column1 datatype varchar(3)

when we try to insert '123990085476'
when we try to insert '123456789'
i.e > 3 varchar
it cuts it off at 3 characters
so
when we insert it treats as dups
123
123

but the actual data if we see if totally different
'123990085476'
'123456789'

Onyxtacular · October 19, 2018, 12:12pm

Sorry I didn't respond properly to this. I'm unsure of the query you wanted me to perform.

Onyxtacular · October 19, 2018, 12:18pm

That makes sense, but I would think since MAXID is a Primary key Identity it wouldn't matter. Apparently it does.

harishgg1 · October 19, 2018, 12:20pm

Hi what I mean is

Your Doubt

create data script

USE tempdb 
go 

drop table #data 
go 

create table #data
(
name varchar(100),
age int 
)
go 

insert into #data select 'harish',20
insert into #data select 'pam',25
insert into #data select 'pam',25
insert into #data select 'sip',40
go 

select * from #data 
go

SQL

SELECT NAME, 
       Count(AGE) 
FROM   #data 
GROUP  BY NAME 
HAVING Count(AGE) > 1

Result Shows Where Dups ARE

Onyxtacular · October 19, 2018, 4:07pm

ah, thanks for the clarification.
I ran this query:

 select PARPRT_02, count(COMPRT_02)
 from Product_Structure
 where PARPRT_02 like '41%' and COMPRT_02 = '349-00006001-03'
 group by PARPRT_02
 having count(COMPRT_02) >1

with no results

Onyxtacular · October 19, 2018, 4:28pm

I think should have posted this with the first post. Here is the table structure in question. It only shows MAXID as the primary key:

	CREATE TABLE [dbo].[Product_Structure](
		[PARPRT_02] [char](30) NOT NULL,
		[COMPRT_02] [char](30) NOT NULL,
		[EFFDTE_02] [smalldatetime] NULL,
		[FILL01_02] [char](2) NULL,
		[QTYPER_02] [float] NOT NULL,
		[QTYCDE_02] [char](1) NOT NULL,
		[LTOSET_02] [smallint] NOT NULL,
		[TYPCDE_02] [char](1) NOT NULL,
		[SCRAP_02] [smallint] NOT NULL,
		[ECN_02] [char](12) NOT NULL,
		[ACTDTE_02] [smalldatetime] NULL,
		[FILL02_02] [char](2) NULL,
		[ALTPRT_02] [char](30) NOT NULL,
		[REFDES_02] [char](24) NOT NULL,
		[MPNSTR_02] [char](1) NOT NULL,
		[MCOMP_02] [char](3) NOT NULL,
		[MSITE_02] [char](3) NOT NULL,
		[UDFKEY_02] [char](15) NOT NULL,
		[UDFREF_02] [char](25) NOT NULL,
		[XDFINT_02] [int] NULL,
		[XDFFLT_02] [float] NULL,
		[XDFBOL_02] [char](1) NULL,
		[XDFDTE_02] [smalldatetime] NULL,
		[XDFTXT_02] [char](100) NULL,
		[FILLER_02] [char](50) NULL,
		[CreatedBy] [varchar](100) NULL,
		[CreationDate] [datetime] NULL CONSTRAINT [DF__Product_S__Creat__536D5C82]  DEFAULT (getdate()),
		[ModifiedBy] [varchar](100) NULL,
		[ModificationDate] [datetime] NULL CONSTRAINT [DF__Product_S__Modif__546180BB]  DEFAULT (getdate()),
		[MAXID] [bigint] IDENTITY(100000,1) NOT NULL,
		[ALTCDE_02] [char](30) NOT NULL DEFAULT (''),
	 CONSTRAINT [PK_Product_Structure] PRIMARY KEY CLUSTERED 
	(
		[MAXID] 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]

yosiasz · October 19, 2018, 4:31pm

as other have mentioned you need to look for indexes also might not come across when you do the CREATE TABLE. Does this table have indexes like @ScottPletcher mentioned key0

harishgg1 · October 19, 2018, 4:32pm

Hi

I see some constraints
On some columns

Any chance these could be the problem

Can we chat directly some where
Will be easier
WhatsApp