Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Syntax

Posted on 2002-06-26
9
Medium Priority
?
385 Views
Last Modified: 2012-05-04
Saying 2 same tables, exept the first one as not any primary key or indexes because there are duplicate rows.

To avoid this, i copy it in the second one :

USE Database2
GO
INSERT INTO Table2
SELECT DISTINCT
    Field1,
    Field2
FROM Table1

I obtain :
Server : Msg 2627, Level 14, State 1, Line2
Violation of Primary Key constraint ..........

However, when just selecting rows (without INSERT), i obtain all rows without any duplicate.

Any idéas?
0
Comment
Question by:vbadier
9 Comments
 
LVL 2

Expert Comment

by:egomaster
ID: 7110619
I guess "table2" in your example has a primary key field?

Is it empty? Probably there are rows in both table 1 and table2 that are identical?

depending on server you are using you might try (if field1 is the one you have primary key on):

INSERT INTO table2
SELECT DISTINCT Field1, field2
FROM table1
WHERE Field1 NOT IN (Select Field1 from table2)
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7111628

The problem is, incuding every field in your select distinct statement does not gaurantee that you will not select records that share duplicate key values that may have some other field that's different.  To avoid this you want to pick the first or last occurence of a record with the same key value.  Use a temp staging table to add an artificial key to the first table:

(This works on SQL Server - if You're using a different DBMS let me know.)


Select Identity(1,1) MyID, *
Into Table1a From Table1


Insert Into Table2
Select Field1, Field2, . . ., Fieldn
From Table1a
Where MyID In(Select Max(MyID)
              From Table1a
              Group by KeyField1, Keyfield2, . . .)

Here I've incuded more that one keyfield in case it's a composite key.  Note that the MyID field isn't in the final insert/select it's just used temporarily to distinguish one record from the next . . ..




0
 

Expert Comment

by:sunpatil
ID: 7112759
first u truncate the table 2 and then
try instring the records.
Also u have to check that the column u have included as a primary key must be unique in ur 1st table.

r u using sybase.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:vbadier
ID: 7117946
i'm using Ms SQL server 7.
The Table2 in empty.
in Table1, no keys are defined, since when i bulk insert datas, there where duplicates rows.
Then i created Table2 with Keys (primary on field 1), and i wanted to do a DISTINCT statement in the select clause to avoid duplicate rows. This work fine with a simple SELECT DISTINCT statement, bu when i include it in the INSERT INTO one, this raise an error.

Regard's
0
 
LVL 1

Expert Comment

by:baryonic
ID: 7131704
You say that a simple SELECT DISTINCT statement works, so do you mean that as a result of this select, you get no duplicate values of Field 1 (the primary key on Table 2)? If there are no duplicates, then the insert should work, but obviously this is not the case. If there are duplicates, you could try dropping the primark key, insert the records, then drop duplicates from Table 2 before adding primary key.
   delete Table2
   from Table2 a,
        Table2 b
   where a.Field1 = b.Field1
   and a.Field2 < b.Field2

depending on which duplicates you wish to drop
0
 
LVL 1

Expert Comment

by:yourbutler
ID: 7166328
Maybe there's some NULLs?
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7170278
the  "select distinct Field1, Field2" statement will only garantee that the combination of field1 and field2 will be distinct.  It does not mean that field1 (or field2) will only occur once in the resulst set.

Thus if a key exists on field1 in table2.  Your insert will bomb every time regardless of weather table2 is empty or not, because within the results you are trying to insert, the key on field 1 is violated is violated.

You have two options:

Either extend the key in table2 to include all the fields in you distinct statement.

Or

Do somethink similar to what I mentioned above with the staging table . . .
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7265999

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296615
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question