Preventing Deadlock: violation

Showing posts with label violation. Show all posts

Monday, March 26, 2012

PRIMARYKEY VIOLATION

Hi,
I am dealing with merge replication.
For identity columns EVEN seed value is set in Publisher database
and ODD seed value is set in Subscriber database(increment value 2) to avoid
insertion conflicts.
For a particular table, usually the records are inserted from
subscriber(through application) and rarely from publisher. During this change
an error 'Primary Violation' occurs.
(1) What is the reason for this?
(2) Is there any way to avoid this?
(3) How can I get the next identity value to be generated?
Thanks,
Soura
Its hard to say. Use the conflict viewer to see if you can figure out where
the two rows are coming from. Partitioning is the way to avoid this, but it
looks like you have done this.
A DBCC Checkident('tablename') will give you the current value, so add the
increment to it to get the next value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:4208FC88-5A06-46F8-BE66-64EFF7ECDBC4@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> For identity columns EVEN seed value is set in Publisher database
> and ODD seed value is set in Subscriber database(increment value 2) to
> avoid
> insertion conflicts.
> For a particular table, usually the records are inserted from
> subscriber(through application) and rarely from publisher. During this
> change
> an error 'Primary Violation' occurs.
> (1) What is the reason for this?
> (2) Is there any way to avoid this?
> (3) How can I get the next identity value to be generated?
> Thanks,
> Soura
>
>
sql

primary keys

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."

can soemone explain to me why i have this kind of error?

i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!

all the values from of application_input will be stored also in approve_overtime.

sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

Check if you are wanting to insert duplicate id_no into approve_overtime ?

Friday, March 23, 2012

Primary key violation on update

I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-JohnPlease post your DDL + INSERT statements of sample data that are causing the
problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:t7gqs15s3a72ddh2ipem4a80bq8osrng9r@.
4ax.com...
I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-John|||When you use JOIN with non-unique columns in a t-SQL UPDATE statement, you
could get that error. Please post your table structures & sample data along
with expected results for others to better understand and repro your
problem. For details, refer to: www.aspfaq.com/5006
Here is an untested attempt, based on the assumption that you have duplicate
EIDs for each SSN which in turn are duplicates as well:
UPDATE EmpTable
SET EID = ( SELECT MAX( neid.New_EID )
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN
AND NOT EXISTS ( SELECT *
FROM EmpTable emp
WHERE emp.EID = neid.New_EID ) )
WHERE EXISTS( SELECT *
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN ) ;
Do you have keys in all your tables? Keys are mandatory in all tables.
Without them, you are mostly left with unmanageable mess.
Anith|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:

>Please post your DDL + INSERT statements of sample data that are causing th
e
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||What are the desired results of the UPDATE, i.e. what should the rows in
EmpTable look like after the update?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:9qhqs19sf1e2qi3nqc29mq0ud9p8terd1a@.
4ax.com...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:

>Please post your DDL + INSERT statements of sample data that are causing
>the
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||"Anith Sen" <anith@.bizdatasolutions.com> wrote:

>Do you have keys in all your tables? Keys are mandatory in all tables.
>Without them, you are mostly left with unmanageable mess.
I posted the DDL just after this post. Keys are not mandatory but what
I inherited is certainly a mess.
-John|||>>Do you have keys in all your tables? Keys are mandatory in all tables.
> I posted the DDL just after this post. Keys are not mandatory
I think he meant theoretically, not technically. What is a table without a
key? In most cases, a mess, by definition.|||John Baima <john@.nospam.com> wrote:

>I am getting a primary key violation with an update.
As I've looked at these tables for awhile, I think that I can solve
the problem by filtering out duplicates. Is there a general way of
finding duplicate records and then deleting just one? In this case, I
really don't care which record is deleted.
Thanks!
-John|||>> Is there a general way of finding duplicate records and then deleting
KBA ( support.microsoft.com ): 139444
Anith|||On Tue, 17 Jan 2006 19:15:06 GMT, John Baima wrote:

>I am getting a primary key violation with an update.
>My update statement is like
>update e
> set e.EID = neid.New_EID
>from EmpTable e
> inner join NewEID neid on neid.Tech_SSN = e.SSN
>and neid.New_EID not in (Select EID from EmpTable)
>The problem is that the final (Select EID from EmpTable) is not
>refreshed after each update
(snip)
Hi John,
"Each update"? There's only one update in this code!
Remember that SQL is a set-based language. That extends to the
interpretation of data modification statements as well. Rows are not
updated one by one, as you seem to think. The entire results of the
UPDATE statement are first built in a temp holding place; after that,
all affected rows are changed, all at the same time.
The actual implementation doesn't have to follow this to the letter, but
the results should be the same as if it does.
Hugo Kornelis, SQL Server MVP

Primary Key Violation Error in SQL 2005

Hey guys...

I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alotHey guys...

I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alot

Thats strange,you should get an error for that in MSSQL 2000 also.|||Can you post DDL (without editing) for the victim table..?

DDL for both tables i.e. table in 2000 & table in 2005.

I used to transfer data, but I didn't face such problem.|||Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?|||Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?

No,that will also give an error of Duplicate key error in your DTS Package execution.|||No,that will also give an error of Duplicate key error in your DTS Package execution.

maybe I am missing something, but I don't see where the kimykimy said they are using DTS for anything. Or are you referring to this statement: "fail package on step failure"?|||maybe I am missing something, but I don't see where the kimykimy said they are using DTS for anything. Or are you referring to this statement: "fail package on step failure"?
exactly ;)|||thanks for your replies. You can ignore my statements on "fail package on step failure." I am not using DTS for the import. Its an insert statement created from a recordset in a vb application.

In SQL 2000 when a duplicate record tries getting inserted it's ignored and moves on to the next record. In SQL 2005 when a duplicate record tries getting inserted I'm getting the "Duplicate Key" error. And the vb code is the same.

Not sure if this helps, but previously this error was resolved by re-restoring the database. Could this be something that was overlooked during the restoration procedure?|||if you are not using DTS, then my previous comment applies. This exact behavior can happen if you have a PK that was created with IGNORE_DUP_KEY=ON (in that case, dupes will be ignored and not inserted).

so check if the PK is IGNORE_DUP_KEY=ON on the 2000 box and IGNORE_DUP_KEY=OFF on the 2005 box.|||I created a new index with the IGNORE_DUP_KEY=ON but when I'm getting an error message "Duplicate Key was ignored" when a dupilcate is encountered.

What I don't understand is I have another table that retreives data in the same method but from a different source without IGNORE_DUP_KEY enabled and it ignores duplicates and continues processing.

Is there a way to ignore this error message from appearing? Thanks|||I created a new index with the IGNORE_DUP_KEY=ON but when I'm getting an error message "Duplicate Key was ignored" when a dupilcate is encountered.

What I don't understand is I have another table that retreives data in the same method but from a different source without IGNORE_DUP_KEY enabled and it ignores duplicates and continues processing.

Is there a way to ignore this error message from appearing? Thanks

Have you created that table in question?If not, then check for any trigger in that existing table...otherwise I think you should get an error ,and thats the way MSSQL works...sql

Primary Key Violation Error

SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
GO
-----
RECORDS:
11652IORESI73756002065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26 09:53:17.237
12207IORITC
73755872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
13208IORYLM
73751692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
143389IOMINA
73755882065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
15213IOALMA
73750872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
16651IORYAC
73756712065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
174578IOBASH
73755452065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
184573IODARM
73755052065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
194579IOALQA
73750062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
20206IOBURJ
73751102065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
21209IOLEMJ
73751502065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
22647IOBABV
73756382065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
23210IOJUBC
73751432065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
24205IOJUMB
73753362065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
253418IOGHYT
73756062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
263422IOFMDX
73756702065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
274731IOOASB
73753062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
284732IOSJUM
73755692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
294724IODXMB
73751602065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
304730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
314730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
324726IOGHDB
73752632065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
334809IOMADI
73753472065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
344725IOHILJ
73757072065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
354611IOGROS
73750862065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
364727IOHATA
73749942065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
374729IOJEBA
73750122065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
384728IOHYDX
73756812065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.

> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>
|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
Anith

Primary Key Violation Error

SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
----
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
G
-----
RECORDS
----
11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
12 207 IORITC
7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
13 208 IORYLM
7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
14 3389 IOMINA
7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
15 213 IOALMA
7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
16 651 IORYAC
7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
17 4578 IOBASH
7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
18 4573 IODARM
7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
19 4579 IOALQA
7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
20 206 IOBURJ
7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
21 209 IOLEMJ
7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
22 647 IOBABV
7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
23 210 IOJUBC
7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
24 205 IOJUMB
7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
25 3418 IOGHYT
7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
26 3422 IOFMDX
7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
27 4731 IOOASB
7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
28 4732 IOSJUM
7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
29 4724 IODXMB
7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
30 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
31 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
32 4726 IOGHDB
7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
33 4809 IOMADI
7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
34 4725 IOHILJ
7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
35 4611 IOGROS
7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
36 4727 IOHATA
7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
37 4729 IOJEBA
7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
38 4728 IOHYDX
7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
----> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
--
Anith

Primary Key Violation Constraint, how to debug.....

Hi all,
I have a stored procedure (from a vendor) that attempts to insert some
records.. Unfortunately, its a very buggy early version, and tech support is
sketchy at best, so I'm trying to figure out the problem myself..
This is the error I'm getting:
Server: Msg 2627, Level 14, State 1, Procedure
usp_SAIncShipToDeliveryLocation, Line 87
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
object 'SA_ShipToDeliveryLocation'.
This is the code fragment that is causing the problem. I'd like to identify
the specific record that is causing primary key violation.. Is this
possible?
insert into SA_ShipToDeliveryLocation
(ShipToDeliveryLocationID
,ShipToDeliveryLocationName
,ShipToDeliveryLocationState
,ShipToDeliveryLocationZip
,ShipToDeliveryLocationCountry
,RegionKey
,CustomerKey
,ShipToDeliveryLocationKey
)
select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
when 0
then r.RegionKey
else null
end RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
from #SASTemp_ShipToDeliveryLocation sd
left join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A'
order by c.CustomerKey
,sd.ShipToDeliveryLocationID
set @.nError = @.@.error
if (@.nError <> 0)
begin
rollback tran;
return @.nError;
endTake your select statment, remove all columns but those in the PK column (or
columns), group by these columns and add a having clause (having count(*) >
1). Off hand, I think the use of a cross join is suspicious (unless there
is only one row in the table). Another thing to check is a poorly written
insert trigger (but that should generate an error with the trigger name in
it).|||There are 2 possible causes for this error. Either a row with the PK value
already exists in the target table or the select statement is returning more
that one row with the same key.
Assuming ShipToDeliveryLocationKey is the primary key of
SA_ShipToDeliveryLocation, you can include your source query as a derived
table to easily identify problem data. See untested examples below.
The CROSS JOIN looks suspect here since this will effectively multiply the
number or rows returned. You'll get the PK error if the
SA_ControlParameters table contains more than one row.
--keys that already exist
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
WHERE EXISTS
(
SELECT *
FROM SA_ShipToDeliveryLocation target
WHERE target.ShipToDeliveryLocationKey =
source.ShipToDeliveryLocationKey
)
--keys that duplicated in source query
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
JOIN
(SELECT sd.ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r ON sd.RegionID = r.RegionID
ANDr.RegionType = 'R'
JOIN SA_Customer c ON c.CustomerKey = sd.CustomerKey
CROSS JOIN SA_ControlParameters cp
WHERE sd.AddChangeDelete = 'A'
GROUP BY sd.ShipToDeliveryLocationKey
HAVING COUNT(*) > 1) dups ON
dups.ShipToDeliveryLocationKey = source.ShipToDeliveryLocationKey
Hope this helps.
Dan Guzman
SQL Server MVP
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>|||Thanks for the advice guys. Dan I'll give the derived table a shot and get
back to
Thanks very much
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>

Primary Key Violation - Transactional Replication

Hi All,
I have setup a transactional replication between 2 SQL 2005 servers.
Unfortunately, I am getting the error listed below:
Replication-Replication Distribution Subsystem: agent
JFCIS3TRM02-JFJDAT-JFJDAT_PUBLICATION-JFCIS3TRM01-13 failed.
Violation of PRIMARY KEY constraint 'ARSTRUN_KEY_0'. Cannot insert duplicate
key in object 'dbo.ARSTRUN'.
Is this error being raised because I haven't enabled automatic range
management? If so, how would I fix this error?
Regards,
JN
Hi Paul,
The type of transactional replication setup is just the plain one, not the
one with updatable subscription.
I am not sure whether it is nosync or automatic as I used the wizard and I
didn't recall being asked for those settings.
Regards,
JN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWzOLQD2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>I really need to know what type of transactional replication setup you have
>configured - plain, updatable (immediate or queued) and nosync or
>automatic...
> Cheers,
> Paul Ibison
>
|||That could be possible since I have created an ODBC connection to the
replicated database which an end user can select from a drop down list when
they open the application.
What do you suggest I do to get the two databases synchronized again?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OjdWiMF2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> OK - in that case my suspicion is that someone has entered a row on the
> subscriber. Is that possible? In this plain transactional case the
> subscriber data is supposed to be read only and only changed via the
> distribution agent.
> HTH,
> Paul Ibison
>

Primary key violation

Hi,
I have got a very peculier kind of problem. My package is running on SQL 2000. There is a identity primary key in a table. Now when I submit the data from 2 different computer at the same time. Only one data is storing. The reason behind this is the primary key violation. as both the data are sending the request to the database at the same time.............n as the primary key is th identity column, it is storing one that value which is able to store the data at the forst hand.
Now plz help me out in this regard............. :confused:Help you do what?

Eliminate the dups, Or remove the constraint?|||The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.|||The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.

Really...man I hate surrogates....|||rdajabarov's solution is the cleanest, but tsk, tsk,... should'a used GUIDs... ;)

Gotta love those surrogate (GUID) keys!|||I'm confused, are you inserting the values into the identity column on the target table or letting target table generate the identity value?

Blindman what's the storage size for a GUID?|||binary(16)|||bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:

1. Will have to completely redesign the db, and what's most painful, - redesign the app.
2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.|||CREATE TABLE [dbo].[vfar_Bact_phylum_tb] (
[Bact_sr_phylum] [int] IDENTITY (1, 1) NOT NULL ,
[Bact_phylum] [varchar] (30) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[vfar_Bact_phylum_tb] WITH NOCHECK ADD
CONSTRAINT [PK_vfar_Bact_phylum_tb] PRIMARY KEY CLUSTERED
(
[Bact_sr_phylum]
) ON [PRIMARY]
GO

This is my table structure. Now from two different computers i'm sending the data to be submitted to this table at the same time. But unfortunately only single data is being saved.

All i want is to save both the data, no matter.........how many simultaneous request is going to the DB.

No, not at all.........its not at all possible to change the table design at this point of time.|||Then get rid of the constraint|||...and of IDENTITY property.|||bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:

1. Will have to completely redesign the db, and what's most painful, - redesign the app.
2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.

1) Even worse, will have to redesign source apps.
2) GUIDS are a bitch to type, but users shouldn't be entering them anyway. I think surrogate keys should be absolutely invisible to the users.

But yeah, it's too late for this guy's purpose.sql

Primary Key Violation

Hi Everyone
I am occasionally getting the following error ......
Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplicat
e key in object 'TABLE1'
The sProc segment that is causing this error is ....
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field2 = field2 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
----
Where field1 and field2 is the composite primary key.
Any ideas how to modify my sProc to stop the primary key violation happening
Cheers
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try,
IF EXISTS (SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
if exists(SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2
+ 1)
print 'tell us what to do in this case.'
else
UPDATE
TABLE1
SET
field2 = field2 + 1
WHERE
field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
AMB
"Peter" wrote:

> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplic
ate key in object 'TABLE1'
> The sProc segment that is causing this error is ....
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field
2)
> UPDATE TABLE1
> SET field2 = field2 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
> ----
-
> Where field1 and field2 is the composite primary key.
> Any ideas how to modify my sProc to stop the primary key violation happeni
ng
> Cheers
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||"Peter" <peter@.dwstech.com> wrote in message news:427c1f0e$1_2@.127.0.0.1...
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert
> duplicate key in object 'TABLE1'
You need to check if the row referenced by (@.field1, @.field2 + 1) exists
before you try to UPDATE. For instance, consider the following:
Field1 | Field2
100 | 100
100 | 101
In this instance if @.field1 = 100 and @.field2 = 100, the following will
cause a PK violation:
UPDATE TABLE1
SET field2 = field2 + 1
WHERE field1 = @.field1
AND field2 = @.field2
What exactly are you trying to accomplish? Maybe someone can help with the
logic, if you can supply more info...|||Oooops, my bad
The sProc code shouls have read ...
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
----
The object of the table is to act as a simple counter. If the primary key al
ready exists in the table then update the counter column (field3) by increme
nting by 1. If the primary key does not exist then insert the primary key an
d set the counter column to
1.
Sorry for the confusion.
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Are you still getting the error? If so then I would guess this to be a
concurrency issue. How busy is this database? Is it likely that two
processes would cause this to occur? If so, there are two things you can
do:
--easy, no other changes to you system possiblilty
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--we don't want anyone to touch the row:
BEGIN TRANSACTION
--lock it exclusively so no one else can read it
IF EXISTS (SELECT 1 FROM TABLE1 (xlock) WHERE field1 = @.field1 AND field2 =
@.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--alternately, change this from a counter into a very thin table:
create table counter
(
field1 int --needs a different name
counter bigint identity,
actiontime datetime,
primary key (field1, counter)
)
Then just insert rows into this table and use aggregates. You can store
more information about each activity, if you want to get a richer set of
information. This method remove all contention on insert other than the
picking of the next counter value, and that is extremely fast.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427c3742$1_2@.127.0.0.1...
> Oooops, my bad
> The sProc code shouls have read ...
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 =
> @.field2)
> UPDATE TABLE1
> SET field3 = field3 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
> ----
-
> The object of the table is to act as a simple counter. If the primary key
> already exists in the table then update the counter column (field3) by
> incrementing by 1. If the primary key does not exist then insert the
> primary key and set the counter column to 1.
> Sorry for the confusion.
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Louis & Michael
Thank you both for your replies.
I will expand a little further on the purpose of this table.
The table is for counting how many times any particular url on a website is
clicked. The primary key fields of the table are click_hour and url_id. url_
id is passed to the sProc and click_hour is calculated within the sProc as d
atediff(hour,0,getutcdate()
). If an entry exists within the table for the particular click_hour and url
_id, then the click_count field is incremented by 1, else an entry is added
to the table for that click_hour and url_id with the click_count field set w
ith an initial value of 1.
I have chosen this method as only one row needs to exist in the table for ea
ch url for each hour regardless of the number of clicks ... thus if one url
is clicked 1000 times each hour for 24 hours there is only 24 rows in the ta
ble as opposed to 24,000.
This sProc can be called anywhere up to 50,000 times a day so the database i
s reasonably busy.
Michael, the suggestion you gave reverts back to the one entry per click sce
nario which I want to avoid as does the second suggestion offered by Louis.
Therefore, unless anyone can come up with a better suggestion, I am looking
at implementing Louis' first suggestion. Louis, I am just wondering what per
formance impact XLOCK will have on the sProc. Also, is XLOCK a better option
than TABLOCK and if so cou
ld you please explain why.
Thanks again to both of you for taking the time to reply.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||sorry for the slow reply. I have been way busy of late:
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first
>suggestion. Louis, I am just wondering what performance impact XLOCK will
>have on the sProc. Also, is >XLOCK a better option than TABLOCK and if so
>could you please explain why.
>
The important thing difference between xlock and tablock is that one takes a
type of lock, the other locks a certain type of resource. You want SQL
Server to take a lock that means no one else can even look at it., but we
only want it to lock the row we are concerned with, not the entire table.
The one entry per click solution is the best solution because no contention.
Make a view of the data to give you your count, and even update your column
once a day and add these rows to count of the newly inserted ones, but as
long as you don't have too many people trying to update the same row, the
fact that you are single threading acess to the row in TABLE1 should not be
too concerning.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427df11b$1_1@.127.0.0.1...
> Louis & Michael
> Thank you both for your replies.
> I will expand a little further on the purpose of this table.
> The table is for counting how many times any particular url on a website
> is clicked. The primary key fields of the table are click_hour and url_id.
> url_id is passed to the sProc and click_hour is calculated within the
> sProc as datediff(hour,0,getutcdate()). If an entry exists within the
> table for the particular click_hour and url_id, then the click_count field
> is incremented by 1, else an entry is added to the table for that
> click_hour and url_id with the click_count field set with an initial value
> of 1.
> I have chosen this method as only one row needs to exist in the table for
> each url for each hour regardless of the number of clicks ... thus if one
> url is clicked 1000 times each hour for 24 hours there is only 24 rows in
> the table as opposed to 24,000.
> This sProc can be called anywhere up to 50,000 times a day so the database
> is reasonably busy.
> Michael, the suggestion you gave reverts back to the one entry per click
> scenario which I want to avoid as does the second suggestion offered by
> Louis.
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first suggestion. Louis, I am just
> wondering what performance impact XLOCK will have on the sProc. Also, is
> XLOCK a better option than TABLOCK and if so could you please explain why.
> Thanks again to both of you for taking the time to reply.
> Regards
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--

Wednesday, March 21, 2012

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00

Desired results:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30

Any and all help appreciated.

|||

Here you go.

Code Snippet

create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)

go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2

|||

With mycte

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.

Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.

|||

Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Desired results:

Any and all help appreciated.

|||

Here you go.

Code Snippet

|||

With mycte

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

|||

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Desired results:

Any and all help appreciated.

|||

Here you go.

Code Snippet

|||

With mycte

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

|||

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

Tuesday, March 20, 2012

Primary Key Constraint Violation

I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.I would load trhe text file in to a table of the correct structure, but =with no PK constraint. Then use SELECT DISTINCT -- to retrieve the =data you need and insert into final table. (INSERT INTO -- SELECT =DISTINCT -- FROM can by helpful for that part).
Mike John
"Matt" <tkiansoon@.yahoo.com> wrote in message =news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
> I need to insert a text file where might contains duplicate data into
> a table with primary keys using a DTS package. is there anyone out
> there could help me? I have tried couple way to get around, but still
> doesn't work.|||You could load it into a staging table first and then de-dupe it or have a
index with ignore duplicate key on the staging table (but this would slow
the load unless the text file is ordered the same as the index which would
need to be unique clustered) and assumes that the entire row is a duplicate
if the key is.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Matt" <tkiansoon@.yahoo.com> wrote in message
news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.

Monday, March 26, 2012

PRIMARYKEY VIOLATION

primary keys

Friday, March 23, 2012

Primary key violation on update

Primary Key Violation Error in SQL 2005

Primary Key Violation Error

Primary Key Violation Error

Primary Key Violation Constraint, how to debug.....

Primary Key Violation - Transactional Replication

Primary key violation

Primary Key Violation

Wednesday, March 21, 2012

primary key error with INSERT INTO

primary key error with INSERT INTO

primary key error with INSERT INTO

Tuesday, March 20, 2012

Primary Key Constraint Violation

Preventing Deadlock

Blog Archive

About Me