SQL conditional insert on SQL Server 2000

Question

Hi I have 2 tables below:

table1

GroupName PersonName email phone
A         Tom        tom@  123
A         Jen        jen@  223
B         Kim        kim@  232

table2

GroupName PersonName email phone
A         Tom        NULL  1
A         Jen        NULL  1
A         Ken        NULL  1
B         Kim        NULL  1
B         Tai        NULL  1

I need to insert all columns of the additional records from table2 into table1. for example, I need Ken and Tai's records from table2 added to table1. it's running on SQL Server 2000 so I cannot use EXCEPT or INTERSECT.

what do you want to insert?

John Woo
– John Woo

2013-03-08 01:18:51 +00:00
Commented Mar 8, 2013 at 1:18 — John Woo
– John Woo, Commented Mar 8, 2013 at 1:18

John Woo · Accepted Answer · 2013-03-08 01:20:24Z

1

assuming email is unique:

insert into table1
select * from table2
where email not in (select email from table1)

alternative:

insert  into table1
select  a.* 
from    table2 a
        LEFT JOIN table1 b
            ON a.email = b.email
WHERE   b.email IS NULL

edited Mar 8, 2013 at 1:20

John Woo

265k70 gold badges509 silver badges500 bronze badges

answered Mar 8, 2013 at 1:17

Z .

12.8k2 gold badges34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Woo Over a year ago

use JOIN instead of IN :)

John Woo Over a year ago

JOINs are way aster than IN

jiaoziren Over a year ago

sorry - email is actually NULL in table2 - pls see my edit. thanks

Nick.Mc Over a year ago

If you want to get technical, use OUTER JOIN instead of IN. But performance is not an absolute. based on the sample data, IN is more than adequate. You could also use NOT EXISTS which might be faster for a larger dataset

lemil77 · Accepted Answer · 2013-03-08 01:21:00Z

0

this will do it..

Inser Table1
select  GroupName, PersonName, email, phone
from Table2 where  GroupName+'|'+PersonName+'|'+email+'|'+phone 
    not in (select GroupName+'|'+PersonName+'|'+email+'|'+phone from Table1)

Thanks!

@leo.

answered Mar 8, 2013 at 1:21

lemil77

3411 silver badge8 bronze badges

Collectives™ on Stack Overflow

SQL conditional insert on SQL Server 2000

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related