June 7, 2011 at 11:34 am
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)
Select ITEMNMBR from IV00101 where ITEMNMBR not in
(
Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn
)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%';
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'
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 7, 2011 at 11:50 am
You probably have duplicate values in your IV00101 table, so even though they don't exist in your target table, they produce duplicate values when they are both inserted. Try changing your query to SELECT DISTINCT ITEMNMBR.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 7, 2011 at 2:21 pm
same error
Regards
Sushant Kumar
MCTS,MCP
June 7, 2011 at 2:35 pm
Did you change this in the main query or the subquery? Changing this in the subquery won't have any real effect on the outcome.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 7, 2011 at 2:45 pm
apparently you have declared a unique alternate key (i.e. a unique index)
Double check you are checking all your unique constraints (pk/ak) before qualifying a row for insert
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 7, 2011 at 2:48 pm
drew.allen (6/7/2011)
Did you change this in the main query or the subquery? Changing this in the subquery won't have any real effect on the outcome.Drew
I tried changing in both , main and sub query, still same error
Regards
Sushant Kumar
MCTS,MCP
June 8, 2011 at 1:50 pm
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
Regards
Sushant Kumar
MCTS,MCP
June 8, 2011 at 2:14 pm
You need to check this constraint 'AK3tcsINVTB00026_AttribAsgn'
Figure out what it is ( obviously a unique key ) and for which column(s) it goes and figure out what is wrong with the provided data.
(i.e. why doesn't it meet this constraint!)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 8, 2011 at 2:22 pm
iTS A UNIQUE NON CLUSTERED INDEX
The index key columns are
attribgroup asc char (21)
itemattrib asc char (21)
seqnumber asc int
Non key columns to include in the index :--None
Also, wen i click on table in SSMS, there is no constraint, just the indexes and primary key
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 8, 2011 at 3:08 pm
SKYBVI (6/8/2011)
The index key columns areattribgroup asc char (21)
itemattrib asc char (21)
seqnumber asc int
Are these columns NULLABLE? Do they have DEFAULTs? Is the seqnumber an IDENTITY?
Without further information my best guess is that the columns are nullable and you're using a trigger to populate the columns. When you insert one row, it works fine, because only that row contains nulls for all of these columns and the trigger updates the columns so that one or more of these is not null. When you insert multiple records, you have multiple rows with nulls for all of these columns and the insert fails before the trigger can populate the columns to make them unique.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 9, 2011 at 1:35 am
Please post the full ddl of your object tcsINVTB00026_AttribAsgn
(i.e. script its create statement including all indexes and constraints )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 9, 2011 at 7:26 am
drew.allen (6/8/2011)
SKYBVI (6/8/2011)
The index key columns areattribgroup asc char (21)
itemattrib asc char (21)
seqnumber asc int
Are these columns NULLABLE? Do they have DEFAULTs? Is the seqnumber an IDENTITY?
Drew
These columns r not nullable..no defaults...other column dex_row_id is an identity...
Regards
Sushant Kumar
MCTS,MCP
June 9, 2011 at 7:28 am
ALZDBA (6/9/2011)
Please post the full ddl of your object tcsINVTB00026_AttribAsgn(i.e. script its create statement including all indexes and constraints )
USE [RTW]
GO
/****** Object: Table [dbo].[tcsINVTB00026_AttribAsgn] Script Date: 06/09/2011 09:28:20 ******/
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 Kumar
MCTS,MCP
June 10, 2011 at 9:37 am
It looks to me like you are inserting only the ITEMNUMBER field, with no values for the key fields (tcsFLST_AttribGroup,[tcsFLST_ItemAtrib) which can not be NULL. You need to have some way of specifying values for these fields, either by assigning them yourself somehow, or by making them identities.
Don Urquhart
June 10, 2011 at 1:44 pm
Don Urquhart (6/10/2011)
It looks to me like you are inserting only the ITEMNUMBER field, with no values for the key fields (tcsFLST_AttribGroup,[tcsFLST_ItemAtrib) which can not be NULL. You need to have some way of specifying values for these fields, either by assigning them yourself somehow, or by making them identities.
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)
For ex...each insert in itemnmbr column values should have 'x' in tcsFLST_AttribGroup
and 'y' in tcsFLST_ItemAtrib
How to do that.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply