where clause with primary key

[lupien1]

Here is two Table:

tab1 tab2
col1 col2 ... col1 col2 ...
1 A 1 C
1 B 1 D
1 C 1 E


In tab1 col1 and col2 are primary key.

I would like to insert the value from tab2 into tab1. What will be the WHERE clause to skip the first row of tab2 and insert the two other rows.

Thank you.

 

[gmmastros]

First, build the select to make sure you get the correct records.

Select tab2.Col1, tab2.col2
from   tab2
       Left join tab1 on tab2.col1 = tab2.col1 
           and tab2.col2 = tab1.col2
Where  Tab2.Col2 is null

If this produces the correct results, then build the insert based on the select

[red]Insert Into tab1(col1, col2)[/red]
Select tab2.col1, tab2.col2
from   tab2
       Left join tab1 on tab2.col1 = tab2.col1 
           and tab2.col2 = tab1.col2
Where  Tab2.Col2 is null

hope this helps

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Try this:

insert into tab1(col1, col2)
select t2.col1, t2.col2
from tab2 t2
left outer join tab1 t1 on t2.col1=t1.col1 and t2.col2=t1.col2
where t1.col1 is null

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

Of course, vongrunt is correct. You'll notice that our queries are similar, but not identical.

Vongrunt's is correct

[red]Where T1.col1 is NULL[/red]

I had tab2 instead of tab1. Sorry for the confusion.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lupien1]

To get more complicated suppose that tab2 have the same name has tab1 and is comming from another database, what will be the WHERE clause.

insert into tab1 select * from openquery(db_access, 'select * from tab1')...