Preventing Deadlock: duplicate

Showing posts with label duplicate. Show all posts

Wednesday, March 21, 2012

Primary Key Problem

OK, I create a table called Customer, I imported date from my as400, when i
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on that
problem I have no more Idea.
Thanks!
JFSeem like the primary key should be the combination of CustId and SubCustId.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||Is the primary key defined on both columns? If it is, try to import the data
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustId.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > OK, I create a table called Customer, I imported date from my as400, when i
> > want to set a primary key it telling me that i have duplicate key.
> > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > have more then one same CustId, but SubCustID have not the same SubCustID.
> >
> > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > can be the same but the sub is always different. Could anyone help me on that
> > problem I have no more Idea.
> >
> > Thanks!
> > JF
>
>|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
> > Seem like the primary key should be the combination of CustId and SubCustId.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > OK, I create a table called Customer, I imported date from my as400, when i
> > > want to set a primary key it telling me that i have duplicate key.
> > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > >
> > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > can be the same but the sub is always different. Could anyone help me on that
> > > problem I have no more Idea.
> > >
> > > Thanks!
> > > JF
> >
> >
> >|||I first delete the table and start over every time.
"Jack" wrote:
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
> > I did
> > SELECT CustId, SubCustId FROM table
> > GROUP BY CustId, SubCustId
> > HAVING COUNT(*) > 1
> >
> > But it give me all my data. That seem to be that all my data is duplicate.
> > I try to import the data before put the primary key and after put the
> > primary key and get the same result.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seem like the primary key should be the combination of CustId and SubCustId.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > http://www.sqlug.se/
> > >
> > >
> > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > want to set a primary key it telling me that i have duplicate key.
> > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > >
> > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > can be the same but the sub is always different. Could anyone help me on that
> > > > problem I have no more Idea.
> > > >
> > > > Thanks!
> > > > JF
> > >
> > >
> > >|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
> I first delete the table and start over every time.
> "Jack" wrote:
> > How many times have you try to import the data into SQL table? Did you
> > delete all records in the table before you import the data if you did it
> > multiple times?
> >
> > "Jean-Francois" wrote:
> >
> > > I did
> > > SELECT CustId, SubCustId FROM table
> > > GROUP BY CustId, SubCustId
> > > HAVING COUNT(*) > 1
> > >
> > > But it give me all my data. That seem to be that all my data is duplicate.
> > > I try to import the data before put the primary key and after put the
> > > primary key and get the same result.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > http://www.sqlug.se/
> > > >
> > > >
> > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > >
> > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > problem I have no more Idea.
> > > > >
> > > > > Thanks!
> > > > > JF
> > > >
> > > >
> > > >|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> DTS?
> "Jean-Francois" wrote:
> > I first delete the table and start over every time.
> >
> > "Jack" wrote:
> >
> > > How many times have you try to import the data into SQL table? Did you
> > > delete all records in the table before you import the data if you did it
> > > multiple times?
> > >
> > > "Jean-Francois" wrote:
> > >
> > > > I did
> > > > SELECT CustId, SubCustId FROM table
> > > > GROUP BY CustId, SubCustId
> > > > HAVING COUNT(*) > 1
> > > >
> > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > I try to import the data before put the primary key and after put the
> > > > primary key and get the same result.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > > http://www.sqlug.se/
> > > > >
> > > > >
> > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > >
> > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > problem I have no more Idea.
> > > > > >
> > > > > > Thanks!
> > > > > > JF
> > > > >
> > > > >
> > > > >|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
> I did an connection with client access driver to connect to the as400 and a
> query the as400 to retreive the data.
> "Jack" wrote:
> > Does the total row returned from the "check duplicate" query match SELECT
> > COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> > DTS?
> >
> > "Jean-Francois" wrote:
> >
> > > I first delete the table and start over every time.
> > >
> > > "Jack" wrote:
> > >
> > > > How many times have you try to import the data into SQL table? Did you
> > > > delete all records in the table before you import the data if you did it
> > > > multiple times?
> > > >
> > > > "Jean-Francois" wrote:
> > > >
> > > > > I did
> > > > > SELECT CustId, SubCustId FROM table
> > > > > GROUP BY CustId, SubCustId
> > > > > HAVING COUNT(*) > 1
> > > > >
> > > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > > I try to import the data before put the primary key and after put the
> > > > > primary key and get the same result.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > http://www.solidqualitylearning.com/
> > > > > > http://www.sqlug.se/
> > > > > >
> > > > > >
> > > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > > >
> > > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > > problem I have no more Idea.
> > > > > > >
> > > > > > > Thanks!
> > > > > > > JF
> > > > > >
> > > > > >
> > > > > >|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
>> OK, I create a table called Customer, I imported date from my as400, when
>> i
>> want to set a primary key it telling me that i have duplicate key.
>> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
>> have more then one same CustId, but SubCustID have not the same
>> SubCustID.
>> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
>> can be the same but the sub is always different. Could anyone help me on
>> that
>> problem I have no more Idea.
>> Thanks!
>> JF

Primary Key Problem

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, February 20, 2012

Preventing the loading of duplicate data to a table - Best Option

Start at the file level; sort it, and scrub it with a 3GL program
before you load it. I would also look into Sunopsis. This is an ELT
tool -- it uses native SQL tools to move data rather than adding yet
another ETL language on top of everything.Thanks, I appreciate the tip; however, the constraints of the project
require that this be done in SQL Server. I am interested to know if this
(Primary Key or Unique Constraint) is the best way to do this within the
restrictions that are in place.
*** Sent via Developersdex http://www.examnotes.net ***|||A PK or UNIQUE constraint is the obvious way to generate the exception
condition. It's probably useful also to report the invalid data so you
may want to load to a staging table, without the constraint, and then
write a query using HAVING COUNT(*)>1 to find the duplicate rows.
David Portas
SQL Server MVP
--

preventing duplicate entry for a given foreign key in db table

Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..

hi nb123

try this

Alter Table YOURTABLENAME
Add Constraint NAMEOFCONSTRAINT UNIQUE (COLUMN NAME)

hope it helps

|||

Are you trying to achieve something like this ?

create table checktest(col1int ,col2int ,col3int ,col4int)GOalter table checktestadd constraint [unq_checktest_col123]unique ( col1 , col2 , col3 )insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 1 , 0insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 1 , 1insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 2 , 0insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 2 , 1

Here the second and the fourth insert query will fail as they violate the unique value constraint.

Hope this will help.

|||

Thanks guys,,yes this is what i am trying to achieve...how do i add the constraints in existing table?

col1 col2 col2 col4
1 12 val1 50
2 12 val1 60 >>this shud not be allowed
3 12 val2 30
4 13 val1 233 >>this is okay,as col2 is changed
5 13 val1 1111 >>this shud not be allowed

|||

I've also written the alter table query in my earlier post.

alter table <tablename>
add constraint [<name of the constraint>]unique ( col1 , col2 , col3 )

After you execute this query, the duplicate values for the col1, col2, col3 combination won't be allowed.

|||

Thanks dhimant,i tried doing it using sql server management studio,i wasn't able to select 'alter table'..it was coming grayed..i thought of drop and recreate it with the above changes,i was getting the syntax error.I basically scripted the createTable from menu and tried adding 'alter table clause stuff manually to it and it was giving me syntex error.i am sure i wasn't doing something right...can u please help me with this...

USE [texashsfb]
GO
/****** Object: Table [dbo].[SEASON_SUPPORT_ROLE] Script Date: 10/24/2007 10:00:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEASON_SUPPORT_ROLE](
[SupportRoleID] [int] IDENTITY(1,1) NOT NULL,
[SeasonID] [int] NOT NULL,
[Role] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SEASON_SUPPORT_ROLE] PRIMARY KEY CLUSTERED
(
[SupportRoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/**** manually added ***/
ALTER TABLE [dbo].[SEASON_SUPPORT_ROLE] add CONSTRAINT [roleConstraint]
unique [SupportRoleID,SeasonID,Role]>>> Incorrect syntax near 'SupportRoleID,SeasonID,Role'.
GO
SET ANSI_PADDING OFF

|||

nb123:
unique [SupportRoleID,SeasonID,Role]>>> Incorrect syntax near 'SupportRoleID,SeasonID,Role'.

use ( ) instead of [ ]. Both of them have different meaning and applied for different purposes. So, your modified alter statement would be

ALTER TABLE [dbo].[SEASON_SUPPORT_ROLE]add CONSTRAINT [roleConstraint]unique ( SupportRoleID,SeasonID,Role )

Hope this will help.

|||

okay,it saved fine after i made the above change but its allowing the duplicate entries in the given column,i am not getting any unique key violation sort of error.Also when i script the table as create,to see the syntax,this is what i see..what iam doing wrong,i was expecing error while entering the duplicate value in role for a given foreign key..

USE [texashsfb]
GO
/****** Object: Table [dbo].[SEASON_SUPPORT_ROLE] Script Date: 10/25/2007 00:54:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEASON_SUPPORT_ROLE](
[SupportRoleID] [int] IDENTITY(1,1) NOT NULL,
[SeasonID] [int] NOT NULL,
[Role] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SEASON_SUPPORT_ROLE] PRIMARY KEY CLUSTERED
(
[SupportRoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [roleConstraint] UNIQUE NONCLUSTERED .>> this 'NOTCLUSTERED stuff got added after i saved
(
[SupportRoleID] ASC,
[SeasonID] ASC,
[Role] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

|||

I got your problem mate. First you run the following queries, I've explained the solution below the queries:

alter table SEASON_SUPPORT_ROLEdrop constraint roleConstraint GOalter table SEASON_SUPPORT_ROLEadd constraint roleConstraintunique ( [SeasonID] , [Role])GO

Now, what is happening is that you've mentioned the SupportRoleId column in your unique key constraint. That column is an identity one and will be auto-incremented, that means the value of this column will always be different than the values already present in your table.

If you've provided more than one columns to be a part of your unique key constraint, then the combination of all the columns should not be repeated and if that happens then only the constraint is said to be violated.

Actually, you don't need the SupportRoleId column in your constraint as it is always going to be unique, hence will make the entire collection of all 3 columns to be unique and that won't violate the constraint and SQL Server will allow these kind of records.

What I've done in the query is to remove the constraint and then recreated it with excluding the SupportRoleId column.

Hope this will help.

|||

ah..that makes sense...i changed the query and now its working as expected..thanks Dhimant for beings so patient with me and helping me with this..

|||

Well, it was a good problem to solve for me too. You should mark as answer the post which solved your problem, that way the member gets credit for solving the problem and other visitors also come to know which post solved your problem.

Preventing duplicate database entries

Hi all.. I've been scouring the forums for about 6 hours to noavail. This is a really simple question. I'm trying to havea registration page that lets the user input name, email, desiredusername, and password. I want to check the username and emailfields to make sure ppl cannot sign up twice. So from what I'vegathered I have a couple of options:

1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.

My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.

since you mentioned sqldatasource I am assuming you are working with SQL Server. You could write a stored proc and do a conditional INSERT.

CREATE PROC..

...

BEGIN

IF NOT EXISTS ( SELECT * FROM table WHERE username = @.username ANDemail=@.email...)

BEGIN

-- do the INSERT

END

ELSE

BEGIN

--the user already exists. return an appropriate message

END

|||Do #1, then catch the constraint error in sqldatasource_inserted event.|||Stored procedures are pretty exciting and cool.. I'm about to spendsome time reading up on them because there's a lot for me to learn (howto execute them, syntax, capabilities, etc.). Appreciate thereply ndinakar. I'll figure this out soon I'm sure.|||I've spent a considerable amount of time trying to set up a constrainton the email and usernames fields to no avail. This is how I'vebeen going about it:

1)in server explorer, double click my table
2)right click the email column, select Check Constraints
3) Add new

Now it asks for an Expression. No idea what to enter here I triedthe single word "UNIQUE" but that is wrong. And, all my web/forumsearches regarding the subject were unsuccessful- lots of regularexpression results returned.

Similarly, I tried playing around with the Indexes/Keys option insteadof check constraints option. I thought this would give me moreluck since it looks like you can select from a drop-down which columnsyou want to apply the effect to. Problem here is the drop-down isonly populated with one column, the PK.

I'm thinking I need to head over to the bookstore and park myself thereuntil I figure some things out- sick of staring at the monitor. Thanks for the reply.|||

You can also do it via script from Query Analyzer:

ALTERTABLE <tableName> ADD COSNTRAINT <constraint_name> UNIQUE(<column1>,<column2>,<column3>)

|||It's not a check constraint, it's really a unique index that you build.

Wednesday, March 21, 2012

Primary Key Problem

Primary Key Problem

Primary Key Problem

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

Monday, February 20, 2012

Preventing the loading of duplicate data to a table - Best Option

preventing duplicate entry for a given foreign key in db table

Preventing duplicate database entries

Preventing Deadlock

Blog Archive

About Me