SQL Question

[alocurto]

I have two tables in my database, lets call them TableA and TableB. What I want to do is excute a query that adds the rows that doesn't exist in both TableA and TableB into TableA. For example:

Before Query:
TableA
1 George
2 Mark
3 Patrick
4 Martin

TableB
1 George
2 Steve
3 Mike

After Query:

TableA
1 George
2 Mark
3 Patrick
4 Martin
5 Steve
6 Mike


Anyone?

 

[CTho9305]

insert into tablea (select * from tableb where id not in (select id from tablea))

or somethign like that? sorry, it's been a while .

 

[alocurto]

its a start, anyone?

 

[alocurto]

People are reccommending joins. I don't think that is needed. Is it?

 

[alocurto]

I have made a little head way but it still isn't what I was looking for. I found ways to do it with multiple queries but that seems over kill. Someone has to know this!

 

[Firus]

I think you would need an 'outer join', thats the only way I can think of doing it...I don't really remember how, but I'm sure you will find something pretty easily by googling. That seems like it would be a pretty simple join.

 

[Argo]

CTho9305 got it. That's all you need, no need for joins of any kind.

 

[sfreeman]

I would do it like this:

insert into tableA
(col1, col2, col3)
(select col1. col2, col3 from tableB
minus
select col1. col2, col3 from tableA);

 

[alocurto]

I am trying that an getting an error

 

[alocurto]

any more ideas?

I am getting an error on the last select: Incorrect syntax near the keyword 'SELECT'. Here is the SQL:

INSERT INTO PRODUCTS (PRODUCTS.product_number, PRODUCTS.family_number, [PRODUCTS.description], PRODUCTS.price, PRODUCTS.delete_flag)
(SELECT PRODUCTS_TEMP.product_number, PRODUCTS_TEMP.family_number, [PRODUCTS_TEMP.description], PRODUCTS_TEMP.price, PRODUCTS_TEMP.delete_flag FROM PRODUCTS_TEMP
MINUS
SELECT PRODUCTS.product_number, PRODUCTS.family_number, [PRODUCTS.description], PRODUCTS.price, PRODUCTS.delete_flag FROM PRODUCTS)

 

[CTho9305]

What SQL server software?

 

[alocurto]

I am using MS SQL 2000. I'm writing the query in query analyzer.

 

[alocurto]

I changed minus to except and it still don't work.

 

[Argo]

Dude, do what ctho9305 said in his original post. That'll work.

 

[alocurto]

This works:
(select product_Number, family_number, description, price, delete_flag FROM PRODUCTS_TEMP WHERE product_Number NOT IN (select product_Number from PRODUCTS))

When I add INSERT INTO PRODUCTS VALUES it does not work. What am I missing? Also, the NOT IN seems very slow. Any ideas?

 

[Argo]

you don't need values clause. just:

insert into tablea (select blah blah ...)

 

[alocurto]

I tried that too:

INSERT INTO PRODUCTS (product_Number, family_number, description, price, delete_flag) VALUES
(select product_Number, family_number, description, price, delete_flag FROM PRODUCTS_TEMP WHERE product_Number NOT IN
(select product_Number from PRODUCTS))

No good. I get the following error output:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.

 

[alocurto]

This looks like it should work. Should I do Joins?

 

[alocurto]

Bumping before bed, can ANYONE help me? I can't find exactly what I need on google.

 

[Mucman]

CTho9305's query will do you want desire, have you not tried it yet?

 

[alocurto]

I have tried it and it does not seem to be working, it is driving me crazy.

 

[alocurto]

Now I am trying:

INSERT INTO PRODUCTS(
product_number,
family_number,
description,
price,
delete_flag)
SELECT
product_number,
family_number,
description,
price,
delete_flag
FROM
(select product_number, family_number, description, price, delete_flag FROM PRODUCTS_TEMP WHERE product_Number NOT IN
(select product_number from PRODUCTS))

Still no good.

 

[alocurto]

Got it:

INSERT INTO PRODUCTS(
product_number,
family_number,
description,
price,
delete_flag)
SELECT
product_number,
family_number,
description,
price,
delete_flag
FROM
PRODUCTS_TEMP
WHERE (PRODUCTS_TEMP.product_Number NOT IN
(select PRODUCTS.product_number from PRODUCTS))