Insert query generating error - SQL Server Forums

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)

Insert query generating error

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-07 : 12:58:04

Hi,
I have a select statement and the output of that statement should be inserted in the already existing table(rtw.dbo.tcsINV...)

My query is :-

Insert into RTW.dbo.tcsINVTB00026_AttribAsgn(ITEMNMBR)
output inserted.ITEMNMBR
Select ITEMNMBR from IV00101 where ITEMNMBR not in
(
Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn
)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%'

order by ITEMNMBR;
IF @@rowcount = 0 -- nothing inserted
PRINT 'No items Inserted';;

Iam getting this error:--
Cannot insert duplicate key row in object 'dbo.tcsINVTB00026_AttribAsgn' with unique index 'AK3tcsINVTB00026_AttribAsgn'.

Also,watever items are inserted in the table, corres to those the other 2 column ( itemtype & address) values should be " xy " and "yz" ..
How can i acheive that

Regards,
Sushant
DBA
West Indies

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 13:04:15

Why do you have the output and the order by?
try this - the distinct will probably fix it.

Insert into RTW.dbo.tcsINVTB00026_AttribAsgn(ITEMNMBR)
Select distinct ITEMNMBR from IV00101
where ITEMNMBR not in (Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-07 : 13:08:01

I got this error now :--

Cannot insert duplicate key row in object 'dbo.tcsINVTB00026_AttribAsgn' with unique index 'AK3tcsINVTB00026_AttribAsgn'.
The statement has been terminated.

Regards,
Sushant
DBA
West Indies

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 13:43:44

Have a look at the index AK3tcsINVTB00026_AttribAsgn.
Is it on the column ITEMNMBR or another column?

Is there a trigger on tcsINVTB00026_AttribAsgn?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-07 : 13:49:03

The index is not on column itemnumber, but on other columns as attribgroup ,itemattrib and seqnumber.

There is no trigger in hte table.

Regards,
Sushant
DBA
West Indies

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-08 : 06:53:55

you aren't inserting attribgroup ,itemattrib and seqnumber so presumably they are being set to null - so you can't insert more than one row without a duplicate - and that's if there isn't a row already in the table which has null values for those columns

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-08 : 11:23:07

I can't get you..
What you mean..plz explain

Regards,
Sushant
DBA
West Indies

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-08 : 13:42:32

attribgroup ,itemattrib and seqnumber is your primary key.

If you already have a row with all nulls for these values, you cannot have another one.

Perhaps FULL DDL and some sample data would help.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-08 : 13:58:13

i dcnt want attribgrp and itematrib to be null for the values i am inserting...
For the insert values, I want to put specific values..
for ex 'x' for attribgrp and 'y' for itemattrib for each isnert values..
I want the data to look like this
Itemnmbr attribgrp itemattrib
coke1 x y
pepsi23 x y
muffin123 x y
What modifications should i do to the query?

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-08 : 15:48:40

I tried running this query (dummy value- 'ABCD1') :-

Insert into dbo.tcsINVTB00026_AttribAsgn(ITEMNMBR) values ('ABCD1')

And it worked fine ...( 1 row affected)

Regards,
Sushant
DBA
West Indies

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 08:05:11

try
Insert into dbo.tcsINVTB00026_AttribAsgn(ITEMNMBR) values ('ABCD1')
Insert into dbo.tcsINVTB00026_AttribAsgn(ITEMNMBR) values ('ABCD2')

If it works check what the attribgroup ,itemattrib and seqnumber values are for those rows - also for the row you inserted successfully.
If they are not null they must be allocated from somewhere and you need to find out how as that is causing your problem.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-09 : 09:37:19

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.tcsINVTB00026_AttribAsgn' with unique index 'AK3tcsINVTB00026_AttribAsgn'.
The statement has been terminated.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-09 : 09:40:35

out of 2 row, the 1 row whcih is inserted( itemnmbr as abcd1)
has no value for attribgrp and itemattrib
seqnmbr as 0 and dex_row_id as 63769.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-09 : 09:42:44

@Donatwork..

The create DDL for that table :--

USE [RTW]
GO
/****** Object: Table [dbo].[tcsINVTB00026_AttribAsgn] Script Date: 06/09/2011 09:43:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[tcsINVTB00026_AttribAsgn](
[tcsFLST_AttribGroup] [char](21) NOT NULL,
[tcsFLST_ItemAtrib] [char](21) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKtcsINVTB00026_AttribAsgn] PRIMARY KEY NONCLUSTERED
(
[tcsFLST_AttribGroup] ASC,
[tcsFLST_ItemAtrib] ASC,
[ITEMNMBR] 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]

GO
SET ANSI_PADDING OFF

Regards,
Sushant
DBA
West Indies

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-10 : 08:37:32

Your primary key is made up from these 3 columns:
tcsFLST_AttribGroup
tcsFLST_ItemAtrib
ITEMNMBR

you are only inserting a value for ITEMNMBR, yet the other columns are getting values.
There must be a trigger somewhere fililng in the other columns.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 09:13:05

Have a look at the response to my previous post.
I suspect this is a unique index rather than a primary key. It allows null values and there is already a row in the table with null values so further inserts using this method fail.

Ways around this are to assign values to these columns or to take null values out of the index (v2005 so you can't).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-10 : 15:47:00

I have data for tcsFLST_AttribGroup and tcsFLST_ItemAtrib...
How do i insert all 3 columns values at a time( itemnmbr values will be derived from the query but other 2 column vlaues are fixed and I know those vaules)
For example...
Each insert in itemnmbr column values should have corresponding 'x' in tcsFLST_AttribGroup
and 'y' in tcsFLST_ItemAtrib

How to do that

Regards,
Sushant
DBA
West Indies

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-13 : 09:16:27

quote:
Originally posted by nigelrivett

Have a look at the response to my previous post.
I suspect this is a unique index rather than a primary key. It allows null values and there is already a row in the table with null values so further inserts using this method fail.

In the DDL it shows the Primary key constraint as

CONSTRAINT [PKtcsINVTB00026_AttribAsgn] PRIMARY KEY NONCLUSTERED
(
[tcsFLST_AttribGroup] ASC,
[tcsFLST_ItemAtrib] ASC,
[ITEMNMBR] 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]

So, where does that leave us for solving this one? Do we need a restatement of WHAT needs done, and a larger set of test data?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-14 : 12:49:21

@ donatwork

The syntax for index is ---
USE [RTW]
GO
/****** Object: Index [AK3tcsINVTB00026_AttribAsgn] Script Date: 06/14/2011 12:48:36 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK3tcsINVTB00026_AttribAsgn] ON [dbo].[tcsINVTB00026_AttribAsgn]
(
[tcsFLST_AttribGroup] ASC,
[tcsFLST_ItemAtrib] ASC,
[SEQNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

So, I guess i cant insert the columns without inserting a unique SEQNUMBR??

Regards,
Sushant
DBA
West Indies

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-14 : 13:29:30

Yep, because it is a Unique index. Same for the primary key. It must also be Unique.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-14 : 13:46:00

PK is my combination of 3 columns and itemnmbr always changes, so I have a unique combination for it..
Only in unique index, i have a problem....
Can I generate random unique numbers for SEQNUMBR and pass thru the same query?
IS it possible?

Regards,
Sushant
DBA
West Indies

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources