Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.
SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
--
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:
> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

duplicate rows but no key on the tables

Dear All,

I have a table with 10 billion records but there are no key on it. I cannot
build a key on it as it is the data source.

However, the data source exits the duplicated rows.

I have used the DTS to transform the data into a new table and delete the
duplicated rows. As there are 10 billion records, i need to divide it into 3
parts and also the process lasts for 6 hours each part.

I want to ask is there any other good methods to slove my problem??

Thx

Estheresther s via SQLMonster.com (forum@.SQLMonster.com) writes:
> I have a table with 10 billion records but there are no key on it. I
> cannot build a key on it as it is the data source.
> However, the data source exits the duplicated rows.
> I have used the DTS to transform the data into a new table and delete
> the duplicated rows. As there are 10 billion records, i need to divide
> it into 3 parts and also the process lasts for 6 hours each part.
> I want to ask is there any other good methods to slove my problem??

Eliminating duplicates from 10 milliard(*) rows is nothing for the
impatient. I'm happy that I don't have to play that game.

(*) I assume. 10 billion rows as in what I mean with billion would be
really dauting...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Duplicate row

hi all,
IF i had one table. in that table three records are there.all there have
same datas.there is no primary key.how i can delete the second row in that
table
regards
balaHi,
How can u say which is the second row?
U want to delete duplicate and want only one row?
Reply
--
Herbert
"balakarthik" wrote:

> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||hello there
try this
use northwind
select p.supplierid, p.unitprice,p.productid from products p
join
(
select supplierid, min(unitprice)as unitprice from products
group by supplierid
) as p2
on p.supplierid=p2.supplierid
and p.unitprice=p2.unitprice
hope it helps
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"balakarthik" wrote:

> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||sorry wrong post
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"balakarthik" wrote:

> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||bala
what do you mean by second row? rows not stored in an order in RDBMS.
CELKO is not going to leave you !!!!!
post ddl/sample data.
Regards
R.D
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> sorry wrong post
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "balakarthik" wrote:
>|||You've haven't seen the rath of Hurricane Celko!
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:F742D9A8-CAC8-4501-B4C3-1374E68FCD83@.microsoft.com...
> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||the best way is to create the table again - correctly [with primary key]
- and insert the distinct values into it from the old table. The new
table will of course have to have a different name.
balakarthik wrote:

>hi all,
>IF i had one table. in that table three records are there.all there have
>same datas.there is no primary key.how i can delete the second row in that
>table
>regards
>bala
>|||>> You've haven't seen the wrath of Hurricane Celko! <<
LOL!!

duplicate reference key values

HI, I keep getting this warning with a lookup (full cache mode) that retreives data form a table that contains the following information:

SRCE_SYS TABLE_NAME FIELD_NAME CODE ENGLISH_DESCRIPTION
STATIC STATIC PM_PC_TX_TYPE_CODE G GROSS
STATIC STATIC PM_PC_TX_TYPE_CODE E EXCESS
STATIC STATIC PM_PC_TX_TYPE_CODE F FACULTATIVE
STATIC STATIC PM_PC_TX_TYPE_CODE S SURPLUS
STATIC STATIC PM_PC_TX_RIDER_CODE BOAT BOAT
STATIC STATIC PM_PC_TX_RIDER_CODE RIDER RIDER
STATIC STATIC PM_PC_TX_RIDER_CODE CONT CONTENTS

The column tab matches SRCR_SYS, TABLE_NAME and FIELD_NAME (using constants defined in a derived column transform) .The code column comes from the source. We want to retreive the english_description colum from SRCR_SYS, TABLE_NAME,FIELD_NAME and CODE in the dataflow.

I would normally ignore the warning but sometimes, it seems that the lookup does not match any values and enabling memory restriction on the advanced tab resolve the issue and suppress the warning.

As I said, I keep getting this warning and I don't know why since there are no duplicates in the table? Am I missing something?

Thank you,

Christian

For the three fields SRCR_SYS, TABLE_NAME, and FIELD_NAME, there are duplicate keys.

You aren't matching on CODE as well?|||

Yes, I match the code as well so the combination of all 4 columns is unique. Does SSIS looks for all columns that are matched in the column tab or just the first one?

Thanks,

Christian

|||It matches on all columns that you select as lookup columns.

The following query should identify any duplicates:
select srce_sys,table_name,field_name,code,count(*) from table group by srce_sys,table_name,field_name,code having count(*)>1

Duplicate primary keys in input file

'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

TIA,

Barkingdog

SORT component has the ability to eradicate dupes. Have you looked into using that?

-Jamie

|||

No, but I will.

BTW does SORT allow me to send those dups to an "erorr" file so I can see I can further investigate those "bad" records?

Barkingdog

|||

No, you can't do that. But you could easily use an AGGREGATE followed by a conditional split to get all those where the key appears more than once.

-Jamie

duplicate primary key error

Im gettig the error "

---

Violation of PRIMARY KEY constraint 'PK_Employees'. "

for the following code..i cant seem to find the error in the code.

---

'data adapter

Dim dAdapt As New SqlClient.SqlDataAdapter

'create a command object

Dim objCommand As New SqlClient.SqlCommand

'command builder

Dim builder As SqlClient.SqlCommandBuilder

'connection string

Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True"

'connection

Dim cnObj As New SqlConnection(cnStr)

'dataset

Dim ds As DataSet

Dim courseID As Integer

Dim courseName As String

Dim desc As String

Dim maxT As Integer

Dim sql As StringBuilder

Dim cmdTxt As String

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

BindData()

End If

End Sub

Private Sub BindData()

connect()

DataBind()

End Sub

Private Sub connect()

'connection

objCommand.Connection = cnObj

'query string

Dim query As String = "SELECT * from StudentPlayground..Courses"

'setting the select command

dAdapt.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection)

'dataset

ds = New DataSet("Course Listings")

cnObj.Open()

dAdapt.Fill(ds, "Courses")

cnObj.Close()

End Sub

Protected Sub submitButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton1.Click

courseID = CInt(CType(FindControl("TextBox1"), TextBox).Text)

courseName = CType(FindControl("TextBox2"), TextBox).Text

desc = CType(FindControl("Textbox3"), TextBox).Text

maxT = CInt(CType(FindControl("TextBox4"), TextBox).Text)

REM Build our parameterized insert statement

sql = New StringBuilder("INSERT INTO Courses ")

sql.Append("(CourseID,CourseName,Description,MaxTrainees)")

sql.Append("VALUES (@.courseID,@.courseName,@.desc,@.maxT)")

cmdTxt = sql.ToString

REM Add parameter values to command

REM Parameters used to protect DB from SQL injection attacks

With objCommand.Parameters

.Clear()

.AddWithValue("@.courseID", courseID)

.AddWithValue("@.courseName", courseName)

.AddWithValue("@.desc", desc)

.AddWithValue("@.maxT", maxT)

End With

objCommand.CommandText = cmdTxt

objCommand.Connection = cnObj

REM Now execute the statement

'connection

cnObj.Open()

objCommand.ExecuteNonQuery()

cnObj.Close()

objCommand.Dispose()

submitButton1.Text = "Record Added!"

End Sub

Well in that particular listing, I don't see any insert into an Employees table. Are you familiar with the PK_Employees primary key constraint in your schema? What table does it exist on (my guess is employees) and on what colum(s) is it on?

I would look at what is going on when the one insert shown above does occur. Are there any triggers entering info that violates this constraint?

If not, I would reanalyze and make sure this is really the section of code causing this problem.

|||checkout whick table has this primary key defined 'pk_employees' (if u r not aware , use the syskeys system table to get that info) . ur code may be adding (repeating)values to that table either directly or thru a trigger.....try to resolve it from there|||

My Apologies...the error is Violation of PRIMARY KEY constraint 'PK_Courses'. Cannot insert duplicate key in object 'dbo.Courses'. This is on the CourseID column in the table Courses. there r no triggers on this table.

aThe strange thing is even though this error appears..my record is still getting inserted correctly in the db. Also this is the ONLY code-beind code present for this page. I just left out the import statements and classdefinition in my post.

|||

The PK constraint error is thrown because of a duplicate "value" (Course) not key attempting to insert into the table.

You can sloppily add On Error Resume Next into your vb.net so you don't see the server side errors, or you check for dupes before you insert records.

Adamus

|||there are no duplicate courses inthe table..i try and add new unique courses each time and experience the error.|||

Do you have a concatenated key or one primary key?

I've experienced similar errors using concatenation and had to remove all constraints and add one key at a time, then one record at a time until I found the error.

Adamus

|||just one primary key on the id field. thats it. no triggers.|||

1. Remove the key - drop the constraint (sp_helpconstraint)
2. Relocate the records
3. Recreate the key
4. Re-import the records

When in doubt, resort to the basics.

Adamus

sql

Duplicate key row error in sql2000 transactional replication

HI All,
We have transactional replication(not updatable) between 2 databses on the
same server (sql server2000).
Our requiremnt is to prevent the deletes to go to subscriber db. So, while
setting up transactional replication entered NONE in article commands
properties tab for DELETE .
Now the deletes are not going to subscriber , but we are getting below error
for one table.
Cannot insert duplicate key row in object 'xx' with unique index 'PK_xx_x_xx'.
The structure of the table1 at the publisher:
Primary Key on xxxID column .
PRIMARY KEY (non-clustered)PK_xx_x_xx on xxxID column
The structure of the table at subscriber:
Index on xxxID
PK_xx_x_xx nonclustered, unique located on secondaryxxxID
The table does not have any clustered indexes defined.
Why we are getting this error? and How to solve this ? Thanks in advance
for your help.
Regards,
Suchi
You are probably getting a deferred update.
Have a look at http://support.microsoft.com/default.aspx/kb/238254
for more info.
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
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:9934FEA4-DBBB-4DF4-B6F2-51D22E25A27E@.microsoft.com...
> HI All,
> We have transactional replication(not updatable) between 2 databses on the
> same server (sql server2000).
>
> Our requiremnt is to prevent the deletes to go to subscriber db. So, while
> setting up transactional replication entered NONE in article commands
> properties tab for DELETE .
> Now the deletes are not going to subscriber , but we are getting below
> error
> for one table.
> Cannot insert duplicate key row in object 'xx' with unique index
> 'PK_xx_x_xx'.
>
> The structure of the table1 at the publisher:
> Primary Key on xxxID column .
> PRIMARY KEY (non-clustered) PK_xx_x_xx on xxxID column
>
> The structure of the table at subscriber:
> Index on xxxID
> PK_xx_x_xx nonclustered, unique located on secondary xxxID
>
> The table does not have any clustered indexes defined.
> Why we are getting this error? and How to solve this ? Thanks in advance
> for your help.
> Regards,
> Suchi
>
>
|||Here is the fix http://support.microsoft.com/kb/302341/EN-US/
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
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:9934FEA4-DBBB-4DF4-B6F2-51D22E25A27E@.microsoft.com...
> HI All,
> We have transactional replication(not updatable) between 2 databses on the
> same server (sql server2000).
>
> Our requiremnt is to prevent the deletes to go to subscriber db. So, while
> setting up transactional replication entered NONE in article commands
> properties tab for DELETE .
> Now the deletes are not going to subscriber , but we are getting below
> error
> for one table.
> Cannot insert duplicate key row in object 'xx' with unique index
> 'PK_xx_x_xx'.
>
> The structure of the table1 at the publisher:
> Primary Key on xxxID column .
> PRIMARY KEY (non-clustered) PK_xx_x_xx on xxxID column
>
> The structure of the table at subscriber:
> Index on xxxID
> PK_xx_x_xx nonclustered, unique located on secondary xxxID
>
> The table does not have any clustered indexes defined.
> Why we are getting this error? and How to solve this ? Thanks in advance
> for your help.
> Regards,
> Suchi
>
>
|||Hi Hilary,
Thanks a ton for your reply. I have gone through the KB article , but that
is for single updtaes only. We are expecting multi row updates also.
Thanks,
Suchi
"Hilary Cotter" wrote:

> Here is the fix http://support.microsoft.com/kb/302341/EN-US/
> --
> 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
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:9934FEA4-DBBB-4DF4-B6F2-51D22E25A27E@.microsoft.com...
>
>
|||Multi row updates are decomposed into single row updates. So if you update
100 rows as part of a batch or in a single stored procedure, they will be
replicated as 100 separate singleton updates.
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
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:FCD2AFB5-4B5B-4DC9-BCA4-2C7AF2BB4871@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks a ton for your reply. I have gone through the KB article , but that
> is for single updtaes only. We are expecting multi row updates also.
> Thanks,
> Suchi
> "Hilary Cotter" wrote:

Duplicate Key problem

Replication is failing with an error message about inserting a duplicate record into a reprint table, what can I do when a duplicate record error is present?
Can I just reinitialize that table?
"JLS" <jlshoop@.hotmail.com> wrote in message news:OpwakUclEHA.1904@.TK2MSFTNGP09.phx.gbl...
Replication is failing with an error message about inserting a duplicate record into a reprint table, what can I do when a duplicate record error is present?
|||enable logging as described in this kb article.
http://support.microsoft.com/default...&Product=sql2k
find the offending row and evaluate whether you can delete it at the
subscriber.
You may want to right click on your distribution agent and stop it and then
select the continue on data constency errors. When you clear the errors you
should run a validation to ensure your databases are in sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uKArPWclEHA.3912@.TK2MSFTNGP12.phx.gbl...
Can I just reinitialize that table?
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OpwakUclEHA.1904@.TK2MSFTNGP09.phx.gbl...
Replication is failing with an error message about inserting a duplicate
record into a reprint table, what can I do when a duplicate record error is
present?

duplicate key insert failure (again)

I'm reposting this because no one responded to my Dec 12 post. There
has to be a way of dealing with upload insert failures. Could someone
please point me in the right direction?
I have one article giving me problems in my topology which involves
multiple Sql Server 2005 Express subscribers to a Sql Server 2005 merge
publication. The article implements an open schema in a many-to-many
relationship with a composite primary key to disallow duplicate
properties...
TABLE seed_properties
seedID int not null
propertyID int not null
value varchar(50) null
rowguid
What I want to happen, when the same property is created at multiple
push subscribers, is that the conflict resolver would choose the one
uploaded to the publisher first and delete the others, replacing them
with the winning property.
What's actually happening is the following...
During subscriber upload the losing subscribers' inserts are failing
with a duplicate key violation, but the subscribers' failing inserts
aren't being removed. So when the publisher attempts to propogate the
winning inserts they fail with another duplicate key violation because
the subscriber didn't delete it's failed inserts.
I WANT the subscriber to delete its failed inserts, but it won't. No
matter what I do in the conflict resolver dialog, the losing inserts
don't get deleted, and so they reappear the next time synchronization
occurs. I've tried all applicable COM conflict resolvers and even my
own custom BusinessLogicModule with no luck.
If I switch it to a pull subscription my BusinessLogicModule may be
able to delete the offending records through an ADO connection to
(local). But before I attempt that, I want to be sure I'm not missing
anything. Am I missing anything? =)
You need to set the compensate_for_errors setting to false. This will kick
back the subscriber inserts and have them overwritten by the publisher
change.
However, you also need to add a datetime column to your table and use the
datetime resolver so the first row in will win. I am not exactly sure how
your conflict handling will work as I am not totally sure what you are
trying to accomplish.
Are you trying to have multiple identical pk occuring on multiple
subscribers between the same sink resolve so the first one in wins for all
subscribers?
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
"sprucely" <timberdig@.gmail.com> wrote in message
news:1166471620.672723.82420@.f1g2000cwa.googlegrou ps.com...
> I'm reposting this because no one responded to my Dec 12 post. There
> has to be a way of dealing with upload insert failures. Could someone
> please point me in the right direction?
> --
> I have one article giving me problems in my topology which involves
> multiple Sql Server 2005 Express subscribers to a Sql Server 2005 merge
> publication. The article implements an open schema in a many-to-many
> relationship with a composite primary key to disallow duplicate
> properties...
> TABLE seed_properties
> seedID int not null
> propertyID int not null
> value varchar(50) null
> rowguid
> What I want to happen, when the same property is created at multiple
> push subscribers, is that the conflict resolver would choose the one
> uploaded to the publisher first and delete the others, replacing them
> with the winning property.
> What's actually happening is the following...
> During subscriber upload the losing subscribers' inserts are failing
> with a duplicate key violation, but the subscribers' failing inserts
> aren't being removed. So when the publisher attempts to propogate the
> winning inserts they fail with another duplicate key violation because
> the subscriber didn't delete it's failed inserts.
> I WANT the subscriber to delete its failed inserts, but it won't. No
> matter what I do in the conflict resolver dialog, the losing inserts
> don't get deleted, and so they reappear the next time synchronization
> occurs. I've tried all applicable COM conflict resolvers and even my
> own custom BusinessLogicModule with no luck.
> If I switch it to a pull subscription my BusinessLogicModule may be
> able to delete the offending records through an ADO connection to
> (local). But before I attempt that, I want to be sure I'm not missing
> anything. Am I missing anything? =)
>

Duplicate key ignored !

Hi guys,

i have the following tables:

Table1:
[PKID] WITH INDEX "IGNORE DUPLICATE KEY"

Table2:
[FKID]

and i have the following SQL statement:

insert into Table1
select distinct FKID
from Table2
where FKID not in (select PKID from Table1)

The above SQL statement is in a DTS package which raises "Duplicate key ignored" error... i can't see how that can happen since i am already checking the key if it already exists. ..

Please help !!!

TNT :sDuplicates are probably in Table2.

Not that this has ever happened to me, but I read about it in this book once. ;)

-PatP|||Thanks for replying pat. :)

Duplicates are not selected from table2 since the 'distinct' keyword is used.

Very wierd problem... i don't have much hair left to pull out :s

T.|||Without having sample tables and data, it is tough (near impossible) to eliminate all of the possibilities. My guess is that you have non-duplicate values in table2 that become duplicates in table1 (this often happens due to type changes, etc).

If you can post the table definitions and some sample data, I'm certain that one of us can figure out your problem... Without them, all we can do is guess... Your description of the problem probably won't help us much.

-PatP|||Thanks for your response.
i will post some data when the problem occurs again.

Thanks again.
TNTsql

Duplicate Key Ignored

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

Since you want to avoid inserting rows from the source already existing in the destination, have you tried inserting based on a select that excludes those existing rows?

One way to construct it, is by a LEFT JOIN query.

INSERT destination
( col1, col2 ..... )
SELECT col1, col2 ....
FROM source s
LEFT JOIN
destination d
ON s.pk = d.pk
WHERE d.pk IS NULL

/Kenneth

|||A better way in that case is to use a subselect:

INSERT INTO destination
(col1, col2, ...)
SELECT col1, col2, ...
FROM source
WHERE pk NOT IN (
SELECT pk
FROM destination
)|||

Rick Brown wrote:

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

I have the same problem, when my app runs the stored procedure, the error appears and the data is not retrieved. I don't think I can use the subquery solution, so I would like to get it working with Ignore_Dup_Key.

Isn't the purpose of Ignore_Dup_Key to allow the data to be returned without inserting the duplicated data?

Thanks for your time

Duplicate key error

I have a table that I use an INSERT command to add a new record. The
primary key is an int field and I am calculating the highest # on table and
incrementing by 1 just before INSERT. On rare occasions, 2 people get the
same number and I get SQL error about duplicate. Is there a way to trap
this or prevent it and just assign a number 1 higher and try INSERT again
(note I cannot use Identity)? Thanks.
David"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
>I have a table that I use an INSERT command to add a new record. The
>primary key is an int field and I am calculating the highest # on table and
>incrementing by 1 just before INSERT. On rare occasions, 2 people get the
>same number and I get SQL error about duplicate. Is there a way to trap
>this or prevent it and just assign a number 1 higher and try INSERT again
>(note I cannot use Identity)? Thanks.
>
Which is exactly like saying "I am having trouble driving this nail with my
screwdriver (note I cannot use a hammer)."
IDENTITY is the right tool for the job. Everything else is an inferior
workaround. Now to get your inferior workaround to actually work, you must
wrap the select max(ID) and INSERT in a serializable transaction. Or
something like
BEGIN TRANSACTION
SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
insert T (ID,...) valueS (@.ID,...)
COMMIT TRANSACTION
BTW, you can perhaps use an IDENTITY column on a different table to generate
your ID's, eg
http://groups-beta.google.com/group...bcc24
68
David|||David,
How are you doing this?
AMB
"David C" wrote:

> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David
>
>|||Thank you. I agree, I always use Identity but have to wait for an old
conversion before I can change it to an identity. BTW, once I make it an
Identity field will it automatically set the SEED, or will I have to do that
with DBCC?
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
> Which is exactly like saying "I am having trouble driving this nail with
> my screwdriver (note I cannot use a hammer)."
> IDENTITY is the right tool for the job. Everything else is an inferior
> workaround. Now to get your inferior workaround to actually work, you
> must wrap the select max(ID) and INSERT in a serializable transaction. Or
> something like
> BEGIN TRANSACTION
> SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
> insert T (ID,...) valueS (@.ID,...)
> COMMIT TRANSACTION
> BTW, you can perhaps use an IDENTITY column on a different table to
> generate your ID's, eg
> http://groups-beta.google.com/group...bcc
2468
> David
>|||David C wrote:
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on
> table and incrementing by 1 just before INSERT. On rare occasions, 2
> people get the same number and I get SQL error about duplicate. Is
> there a way to trap this or prevent it and just assign a number 1
> higher and try INSERT again (note I cannot use Identity)? Thanks.
> David
You need to keep things locked in a transaction in order to prevent
users from stepping on one another. I generally prefer using another key
table that dishes out next key values if an identity can't be used.
In your case, you need to do something like this:
Begin Tran
Select @.NextID = MAX(ID) + 1 From TableA WITH (UPDLOCK, HOLDLOCK)
Insert TableA
Commit Tran
David Gugick
Imceda Software
www.imceda.com|||David,
You can also use a subquery in your INSERT statement, in this case you
don't need to use a transaction.
Instead of this:
insert MyTable values(ID, Col2, Col3, ..., ColN)
use this:
insert MyTable
select ID = IsNull((select max(ID+1) from MyTable), 1),
Col2,
Col3,
..,
ColN
Shervin
"David C" <dlchase@.lifetimeinc.com> wrote in message news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.ph
x.gbl>...
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David|||You'll need to recreate the table if you need to convert the existing values
to IDENTITY. You can't add the IDENTITY property to an existing column.
Hope this helps.
Dan Guzman
SQL Server MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:eHQjdv6DFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Thank you. I agree, I always use Identity but have to wait for an old
> conversion before I can change it to an identity. BTW, once I make it an
> Identity field will it automatically set the SEED, or will I have to do
> that with DBCC?
> David
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
>|||> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
You can't prevent the assignment of duplicate values without a transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:4eaef17a.0502101720.4d2016f@.posting.google.com...
> David,
> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
> Instead of this:
> insert MyTable values(ID, Col2, Col3, ..., ColN)
>
> use this:
> insert MyTable
> select ID = IsNull((select max(ID+1) from MyTable), 1),
> Col2,
> Col3,
> ...,
> ColN
> Shervin
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl>...|||Dan,
You are absolutely right. I just tested my code and it failed. I
thought SQL Server would automatically start a transaction to protect
the execution of subquery, but I was wrong. Thanks for correcting me.
Regards,
Shervin
Dan Guzman wrote:
you
> You can't prevent the assignment of duplicate values without a
transaction.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:4eaef17a.0502101720.4d2016f@.posting.google.com...
you
The
table
get
to trap
INSERT again

Duplicate key error

I'm having trouble when trying to apply a snapshot to the subsciber. The
distibution agent reports error "unexpected EOF found in BCP data file error"
in addition it reports " duplicate key error". I have filtered the data
being sent and choosen to delete the rows at the subscriber table that match
the filter. I've checked the subscriber table and all looks ok. The
snapshot application has deleted the rows that are in the filter but when it
inserts the filtered rows it's showing a "duplicate key error". How can this
be? Can any one explain this?
I found that dropping and recreating the table does work however, I don't
want to drop and re-create the table as there is existing data in the
subscriber table that needs to be preserved.
Thanks,
Reana
Can you have a look at the snpashot files to confirm what is happening. There
should be a delete statement with a where clause and the bcp data file should
only have rows that correspond tot he where clause. Also check for any
duplicates in the bcp data files.
Cheers,
Paul Ibison

duplicate key

i am getting
can not insert duplicate key row in objec
how to solve with help of profiler i mean which recopred or row is the main
problem who makes duplicates not allowing updates to come in db
--
m.abduInsert into a staging table first, then query to see which row would be the
duplicate.
"mohamed abdu" <mohamedabdu@.discussions.microsoft.com> wrote in message
news:CFFD1E37-3CF1-41C4-9987-6FAEE10C7DF8@.microsoft.com...
> i am getting
>
> can not insert duplicate key row in objec
> how to solve with help of profiler i mean which recopred or row is the
main
> problem who makes duplicates not allowing updates to come in db
> --
> m.abdu|||mohamed
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"mohamed abdu" <mohamedabdu@.discussions.microsoft.com> wrote in message
news:CFFD1E37-3CF1-41C4-9987-6FAEE10C7DF8@.microsoft.com...
>i am getting
>
> can not insert duplicate key row in objec
> how to solve with help of profiler i mean which recopred or row is the
> main
> problem who makes duplicates not allowing updates to come in db
> --
> m.abdusql

duplicate key

i am getting
can not insert duplicate key row in objec
how to solve with help of profiler i mean which recopred or row is the main
problem who makes duplicates not allowing updates to come in db
--
m.abduInsert into a staging table first, then query to see which row would be the
duplicate.
"mohamed abdu" <mohamedabdu@.discussions.microsoft.com> wrote in message
news:CFFD1E37-3CF1-41C4-9987-6FAEE10C7DF8@.microsoft.com...
> i am getting
>
> can not insert duplicate key row in objec
> how to solve with help of profiler i mean which recopred or row is the
main
> problem who makes duplicates not allowing updates to come in db
> --
> m.abdu|||mohamed
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"mohamed abdu" <mohamedabdu@.discussions.microsoft.com> wrote in message
news:CFFD1E37-3CF1-41C4-9987-6FAEE10C7DF8@.microsoft.com...
>i am getting
>
> can not insert duplicate key row in objec
> how to solve with help of profiler i mean which recopred or row is the
> main
> problem who makes duplicates not allowing updates to come in db
> --
> m.abdu

Duplicate Foreign keys.

Hi all,

SQL server allows to create as many as foreign key constraints on a same table for a same column.

Will this affect the design or performance in anyway ?

Naming the constraint would be a good way to avoid this.But in case if someone has already created, How do I remove the existing duplicate keys ?

======================
For Example , I have 2 tables Author and Book. I could execute the below query n times and create as many as foreign keys I want.

ALTER TABLE Books
ADD
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)

======================

Thanks is advance,

DBAnalyst

this may affect the performance. foreign keys shud be named..eg

ALTER TABLE Books with nocheck

ADD constraint fkname

FOREIGN KEY (AuthorID)

REFERENCES Authors (AuthorID).

if u want to check the existing foreign keys to check ne duplicacy use the REFERENTIAL_CONSTRAINTS view of information_schema..

select * from information_schema.REFERENTIAL_CONSTRAINTS

duplicate error report

I have an app which downloads data from mainframe daily.
Because the primary key definition is different, I have to verify duplicates
before import to my local table.
I would like to know that is it possible to insert duplicates into a table
to investigate later on.
If yes, where is the best place to do it?
For example may I use insert triggers?
I may insert current record into my table if insert fails.
If I am wrong, any information is great appreciated.
Souris,Hi
If you load the data into a loading table then you could do something like:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable
WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
INSERT INTO DestinationTable
SELECT *
FROM LoadingTable
WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
John
"souris" <soukkris@.viddotron.com> wrote in message
news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>I have an app which downloads data from mainframe daily.
> Because the primary key definition is different, I have to verify
> duplicates before import to my local table.
> I would like to know that is it possible to insert duplicates into a table
> to investigate later on.
> If yes, where is the best place to do it?
> For example may I use insert triggers?
> I may insert current record into my table if insert fails.
> If I am wrong, any information is great appreciated.
> Souris,
>
>|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for the sample code.
This works for one primary key column.
Are there any example for 4 primary key columns ?
Thanks again
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
Try:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable L
WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
INSERT INTO DestinationTable
SELECT *
WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
John
souris wrote:
> Thanks for the sample code.
> This works for one primary key column.
> Are there any example for 4 primary key columns ?
> Thanks again
> Souris,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
something
a|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1108538560.326295.15100@.c13g2000cwb.googlegroups.com...
> Hi
> Try:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable L
> WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> INSERT INTO DestinationTable
> SELECT *
> WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> John
>
> souris wrote:
> something
> a
>sql

duplicate entry in a primary key field

Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubid int Unchecked (primary key)
pub char(1) Unchecked
publ char(1) Unchecked
pubcode char(2) Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

when i save this table modifying the pubid and pubcode as primary keys the following error displays...

Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.

what i understand is that on the primary key duplicates are not allowed how could i allow it?

thanksAlex - you could probably do with a brush up on relational database design & theory.
http://r937.com/relational.html

1) You can't have two primary keys - there can only be one. Most likely you have a composite primary key.
2) The whole point of a primary key is that it is unique - this is pretty well the central tenet of a primary key.

What columns in your table uniquely identify a row? It looks like it could be a combination of pubid and pubcode but please let us know.|||You can have exactly 1 primary key. If I understand correctly, the PK is now a composite key on 2 columns, pubid and pubcode. There are no duplicate combinations of values in that combination of columns, even though both columns individually do contain duplicate values.|||there is no column that uniquely identify a row actually the table is a junction table for many to many relationship pubid is the foreign key for table1 and pubcode for table3 I'm normalizing the database so i created this juction table for tables1 & 3 so with it is it okey if i will not make both a primary key?|||so with it is it okey if i will not make both a primary key?is it okay? well, yes, provided that you really do wish to allow the possibility of the same pubid being related to the same pubcode more than once

in most many-to-many relationships, this would be an error|||so what's the best way i could do it?

thanks|||create a composite primary key on both columns|||You have a composite primary key - this is one primary key comproised of 2+ columns.

i.e.
CREATE TABLE MyTable
(
pubid int
, pub char(1)
, publ char(1)
, pubcode char(2)
, CONSTRAINT pk_MyTable PRIMARY KEY CLUSTERED (pubid, pubcode)
)Again I recommend you read the link. It is only an article not a whole book but it will help you get some of the basics figured out.