January 25, 2007 at 11:11 am
I'm trying to use "Insert ....... Select..." to put some data into a table, and got the error "Insert duplicate key row in object tablename with unique index indexname". The index is a unique clustered index composed of 3 columns. I've compared the data to be inserted with the data already in the table and didn't find any records with identical values for all three columns included in the index, run dbcc dbreindex on the table, but still got the error indicating "Insert duplicate key" thus the insert failed.
SQL Server 2000, sp4, running on Windows Server 2000.
I'd appreciate any idea pointing me to the right direction solving the problem. Thanks!
January 25, 2007 at 12:05 pm
Try this :
Select A.Key1, A.Key2, A.Key3, B.Key1, B.Key2, B.Key3 FROM BaseTable A INNER JOIN OtherTable B ON A.Key1 = B.Key1, A.Key2 = B.Key2, A.Key3 = B.Key3
January 25, 2007 at 12:13 pm
yep, already did that and that's what I mean "didn't find any identical records" in my original posting.
January 25, 2007 at 12:28 pm
Do you have a second unique index on the table or a trigger that inserts into a third table?
January 25, 2007 at 12:48 pm
The table does have another unique index which is an identity column and values are autogenerated by the system. And no triggers.
What puzzles me is that how it detect duplicate for the specified key doing the insert, while I couldn't find any using the inner join query based on the three columns contained by the key.
January 25, 2007 at 1:44 pm
can you post the full table DDL with constraints and indexes?
Maybe we'll spot something you missed.
January 25, 2007 at 2:22 pm
Here's the DDL. The insert error is on the index "UNIQUE CLUSTERED INDEX [IX_members_List_EmailLC]". Thank you for all your help.
USE [ListManager]
GO
/****** Object: Table [dbo].[members_] Script Date: 01/25/2007 15:51:03 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[members_](
[Additional_] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AppNeeded_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[CanAppPend_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[CleanAuto_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[Comment_] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConfirmDat_] [smalldatetime] NULL,
[DateBounce_] [smalldatetime] NULL,
[DateHeld_] [smalldatetime] NULL,
[DateJoined_] [smalldatetime] NULL,
[DateUnsub_] [smalldatetime] NULL,
[Domain_] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmailAddr_] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpireDate_] [smalldatetime] NULL,
[FullName_] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsListAdm_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[List_] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailFormat_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('M'),
[MemberID_] [int] IDENTITY(1,1) NOT NULL,
[MemberType_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('normal'),
[NoRepro_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[NotifyErr_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[NotifySubm_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[NumAppNeed_] [smallint] NOT NULL DEFAULT (0),
[NumBounces_] [smallint] NOT NULL DEFAULT (0),
[Password_] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RcvAdmMail_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[ReadsHtml_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[ReceiveAck_] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('F'),
[SubType_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('mail'),
[UserID_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserNameLC_] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[First_Name_] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last_Name_] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address_1_] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address_2_] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State_Province_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Postal_Code_] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country_] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone_Home_] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone_Office_] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Company_Name_] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Company_Type_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Company_URL_] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City_] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax_Office_] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PermissionGroupID_] [int] NULL,
CONSTRAINT [PK_members] PRIMARY KEY NONCLUSTERED
(
[MemberID_] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [ListManager]
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CONSTRAINT [memberPermGroup] FOREIGN KEY([PermissionGroupID_])
REFERENCES [dbo].[lyrPermissionGroups] ([GroupID])
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([AppNeeded_] = 'F' or [AppNeeded_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([CanAppPend_] = 'F' or [CanAppPend_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([CleanAuto_] = 'F' or [CleanAuto_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([IsListAdm_] = 'F' or [IsListAdm_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([MailFormat_] = 'H' or ([MailFormat_] = 'M' or [MailFormat_] = 'T')))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([NoRepro_] = 'F' or [NoRepro_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([NotifyErr_] = 'F' or [NotifyErr_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([NotifySubm_] = 'F' or [NotifySubm_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([RcvAdmMail_] = 'F' or [RcvAdmMail_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([ReadsHtml_] = 'F' or [ReadsHtml_] = 'T'))
GO
ALTER TABLE [dbo].[members_] WITH CHECK ADD CHECK (([ReceiveAck_] = 'F' or [ReceiveAck_] = 'T'))
CREATE NONCLUSTERED INDEX [IX_members_EmailLC] ON [dbo].[members_]
(
[Domain_] ASC,
[UserNameLC_] ASC
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_members_List_EmailLC] ON [dbo].[members_]
(
[List_] ASC,
[UserNameLC_] ASC,
[Domain_] ASC
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_members_List_NotifyErr] ON [dbo].[members_]
(
[List_] ASC,
[NotifyErr_] ASC
) ON [PRIMARY]
January 25, 2007 at 2:29 pm
Damn, forgot to ask for the ddl on the other table.
Are there any differences in datatypes or collations between the 2 tables?
January 25, 2007 at 2:47 pm
Here's the procedure used to do the insert. It's been used to update the same table (members_) with different data set (different List_) (from the same data source) successfully. So it's not datatype or collations problems. Thanks.
USE [ResDir]
GO
/****** Object: StoredProcedure [dbo].[prcSyncLyrisWithResDir] Script Date: 01/25/2007 16:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[prcSyncLyrisWithResDir]
@FirmType int = 1,
@List varchar(20) = 'cpai-dispatch'
AS
SET NOCOUNT ON
DECLARE @ErrorSave INT
SET @ErrorSave = 0
SELECT LTrim(RTrim(p.Email)) AS Email, f.FirmName, LTrim(RTrim(LOWER(SUBSTRING(p.Email, CHARINDEX('@', p.email)+1, LEN(p.Email))))) AS Domain_,
LTrim(RTrim(LOWER(SUBSTRING(p.Email, 0, CHARINDEX('@',p.Email))))) AS UserNameLC_, @List AS List_,
GETDATE() AS DateJoined_, (p.[First] + ' ' + p.[Last]) AS FullName_
INTO #temp
FROM ResDir.dbo.Personnel p LEFT JOIN ResDir.dbo.Firm f ON p.FirmID=f.FirmID
WHERE f.FirmTypeID=@FirmType AND LEN(p.Email) > 0
AND LTrim(RTrim(p.Email)) NOT IN
(SELECT m.EmailAddr_
FROM ListManager.dbo.members_ m
WHERE LTrim(RTrim(m.List_))=@List)
AND LTrim(RTrim((LOWER(SUBSTRING(p.Email, CHARINDEX('@', p.email)+1, LEN(p.Email)))))+
LTrim(RTrim(LOWER(SUBSTRING(p.Email, 0, CHARINDEX('@',p.Email)))))) NOT IN
(SELECT LTrim(RTrim(m.Domain_))+ LTrim(RTrim(m.UserNameLC_))
FROM ListManager.dbo.members_ m
WHERE LTrim(RTrim(m.List_))= @List)
IF @@Rowcount > 0
BEGIN
begin transaction
INSERT INTO ListManager.dbo.members_ (EmailAddr_, Additional_,
Domain_, UserNameLC_, List_, DateJoined_, FullName_)
SELECT * FROM #TEMP
SET @ErrorSave = @@ERROR
IF (@ERRORSAVE 0)
BEGIN
ROLLBACK TRANSACTION
RETURN @ErrorSave
END
INSERT INTO RESDIR.dbo.RESDIRLYRISSYNCARCHIVE(EmailAddr, FirmName,
DomainName, UserNameLC, List, DateJoined, FullName)
SELECT Email, FirmName, Domain_, UserNameLC_, List_, DateJoined_, FullName_ FROM #TEMP
SET @ErrorSave = @@ERROR
IF (@ERRORSAVE 0)
BEGIN
ROLLBACK TRANSACTION
RETURN @ErrorSave
END
Commit transaction
END
RETURN @ErrorSave
SET NOCOUNT OFF
January 26, 2007 at 2:47 am
I have encountered similar problems in the past, also with tables where the clustered primary key was a somposite of 3 char fields. In each case I solved the problem by dropping and recreating the clustered unique index.
You might want to try this on a copy of the table in a sandbox database or a developmetn server just to be on the safe side.
Tony
January 26, 2007 at 7:33 am
That would be my last resort, but wanted to see if there's an alternative. And I thought DBCC DBREINDEX was supposed to drop and rebuild the indexes, which I had run on alll indexes on the table.
I'll have to drop the other non clustered indexed (or only disable them?) first, then drop and recreate the clustered index, and last, rebuild the other non clustered indexes.
Thank you all for posting suggestions in helping to solve the problem.
January 26, 2007 at 7:47 am
Is there any way you can create a new database and export those 2-3 tables there. Then backup the data and send it to me so I can check it out. I don't have anything else to offer at the moment that I can check without all the data. I know this is a big security issue but I can't offer anything else.
January 29, 2007 at 8:34 am
Ok, I created a new table with the same structure and created the unique clustered index on it, then tried to input the same set of records into this new table, and it gave me the same error, which lead me to look at the input data I prevously assumed should all be unique. And there I found the culprit: a record for a different person but with an email the same as another person!!!
Here's a lesson I learnt: don't assume anything and check everything.
Thank you all for your time and effort in posting responses to this thread.
January 29, 2007 at 9:12 am
Ya nice catch.
RUN DBCC CHECKCONSTRAINTS to see if there are any other tables in this situation.
That can happen if a constraint was added without full validation, or if it was disabled while bad data was inserted. The constraint is then in an untrusted state. I once started to write a script to catch those. The script is still in developpement >>
RUN AT YOUR OWN RISK.
SET NOCOUNT ON
DECLARE @TableName SysName
DECLARE @ConstraintName SysName
DECLARE @Owner SysName
DECLARE @ConstraintText nVarChar(3630)
DECLARE @DynSQL nVarChar(4000)
IF 0 < OBJECT_ID('tempdb..#temp')
DROP TABLE #temp
CREATE TABLE #temp (Table_Name sysname, Owner sysname, Constraint_name sysname, Constraint_Text nvarchar(3630), InvalidData BIT)
--may fail if you have a constraint with more than 3500 characters, but I mean come on guys, don't tell me you need 3500 chars on a single check constraint !!!!
INSERT INTO #temp (Table_Name, Owner, Constraint_Name, Constraint_Text, InvalidData)
SELECT object_name(O.parent_obj) AS Table_Name
, USER_NAME(o.uid) AS Owner
, O.name AS Constraint_Name
, C.Text AS ConstraintText
, 0 AS InvalidData
FROM dbo.sysobjects O
INNER JOIN dbo.SysComments C
ON O.id = C.id
WHERE O.status & 2048 = 2048
AND O.XType = 'C'
ORDER BY object_name(O.parent_obj) --Table_Name
SELECT TOP 1
@TableName = Table_Name
, @Owner = Owner
, @ConstraintName = Constraint_Name
, @ConstraintText = Constraint_Text
FROM #temp
WHERE InvalidData = 0
ORDER BY Constraint_Name
WHILE @TableName IS NOT NULL
BEGIN
SET @DynSQL = 'IF NOT EXISTS (SELECT * FROM [' + @Owner + '].[' + @TableName + '] WHERE NOT ' + @ConstraintText + ') '
SET @DynSQL = @DynSQL + 'BEGIN ALTER TABLE [' + @Owner + '].[' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']' + CHAR(13) + CHAR(10)
SET @DynSQL = @DynSQL + 'ALTER TABLE [' + @Owner + '].[' + @TableName + '] WITH CHECK ADD CONSTRAINT [' + @ConstraintName + '] CHECK ' + @ConstraintText + 'END '
SET @DynSQL = @DynSQL + 'ELSE BEGIN UPDATE #temp SET InvalidData = 1 WHERE Constraint_Name = ''' + @ConstraintName + ''' END'
PRINT @DynSQL
--EXEC (@DynSQL)
DELETE FROM #temp WHERE Constraint_Name = @ConstraintName AND InvalidData = 0
SET @TableName = NULL
SELECT TOP 1
@TableName = Table_Name
, @Owner = Owner
, @ConstraintName = Constraint_Name
, @ConstraintText = Constraint_Text
FROM #temp
WHERE InvalidData = 0
ORDER BY Constraint_Name
PRINT ''
END
SELECT 'Warning, INVALID DATA' AS WARNING, Table_Name, Owner, Constraint_name, Constraint_Text FROM #temp
DROP TABLE #Temp
May 1, 2007 at 9:09 am
may be you have duplicate records into new one:
try that:
INSERT INTO ListManager.dbo.members_ (EmailAddr_, Additional_,
Domain_, UserNameLC_, List_, DateJoined_, FullName_)
SELECT distinct EmailAddr_, Additional_,Domain_, UserNameLC_, List_, DateJoined_, FullName_
FROM #TEMP t
--for excluding duplicates between #temp and members_ you can add that:
left outer join ListManager.dbo.members_ m on t.k1=m.k1 and t.k2=m.k2....
where m.k1 is null
--where k1, k2... are the unique keys
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply