Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Syntax

Posted on 2002-06-26
9
Medium Priority
?
384 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

916 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