Solved

SQL Syntax

Posted on 2002-06-26
9
372 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Read about achieving the basic levels of HRIS security in the workplace.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now