Copy distinct from A to B..? 1

[kenjoswe]

Hi all,

I have a Table1 with with three fields:

A B C
1 2 3
1 2 3
2 3 3

I would like to insert only the distinct rows to Table2 that has primary key defined to column A and column B.
And also insert only the records that doesn't already exist in Table2?

How can I do this?

/Kent J.

 

[branko]

check:

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/183/qid/257363

 

[bperry]

Here's a start (untested):

Insert Into Table2
Select DISTINCT ColA, ColB
From Table1 t1a
Where Not Exists
(select * from Table1 t1b
where t1b.ColA = t1a.ColA and t1b.ColB = t1a.ColB)


How does this do? (I'm assuming tat ColC is not going into Table2. Is that right?)

bperry

 

[bperry]

Yikes, i got the Tables mixed up. Here is a correction.

Insert Into Table2
Select DISTINCT ColA, ColB
From Table1 t1a
Where Not Exists
(select * from Table2 t1b
where t1b.ColA = t1a.ColA and t1b.ColB = t1a.ColB)

 

[kenjoswe]

bperry,

But what about ColC?
"Select DISTINCT ColA, ColB"

I want to insert ColC as well in Table2 but not the duplicate row.

/Kent J.

 

[bperry]

Okay, probably something like this?

Insert Into Table2
Select DISTINCT ColA, ColB, MAX(ColC)
From Table1 t1
Where Not Exists
(select * from Table2 t2
where t2.ColA = t1.ColA and t2.ColB = t1.ColB)
Group By ColA, ColB
--------------------
Notice that since we are summarizing (i.e. Grouping) on ColA+ColB, then we have to decide *which* value to keep from ColC. I have shown the MAX function which is quite common, but you might want something else. In this example data doesn't matter, since they all show as = 3. Is all the data like that?

bperry

 

[kenjoswe]

bperry,

Thanks for your reply!
I have got it to work.

/Kent J.

 

[Rosko]

Or you could use this

Insert Into Table2
Select DISTINCT t1a.ColA, t1a.ColB, t2.ColC
From Table1 t1a, table1 t2
Where Not Exists
(select * from Table2 t1b
where t1b.ColA = t1a.ColA and t1b.ColB = t1a.ColB)
and t1a.ColA = t2.ColA
and t1a.ColB = t2.ColB

Rosko