SQL Syntax

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?
vbadierAsked:
Who is Participating?
 
NetminderCommented:
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0
 
egomasterCommented:
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
 
spcmnspffCommented:

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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
sunpatilCommented:
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
 
vbadierAuthor Commented:
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
 
baryonicCommented:
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
 
yourbutlerCommented:
Maybe there's some NULLs?
0
 
spcmnspffCommented:
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
 
nico5038Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.