1

I want to insert some data of column1 from Table_B to Table_A if the data in Table_B does not exist in Table_A. For example, 'Headache' is in column1 of Table_B but not in column1 of Table_A. Thanks. I wrote the SQL below, but it did not work:

insert into Table_A(column1)
select column1
from Table_B
where column1 not in (select column1 from Table_A)
Ice
  • 429
  • 2
  • 6
  • 19
  • What do yo mean by *"did not work"*? Can you elaborate? Did you get an error? Also, what DBMS are you using? – GarethD Sep 29 '16 at 15:56

4 Answers4

1

Try This:

INSERT INTO Table_A(column1)
SELECT B.column1
FROM Table_B B
LEFT JOIN Table_A A ON B.column1 = A.column1
WHERE A.column1 IS NULL
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
0
Insert into Table_A(column1)
select column1 from Table_B 
left join Table_A  on Table_B.column1 = Table_A.column1
where A.column1 is null
Ritesh Patel
  • 315
  • 3
  • 10
0

With no sample data it is hard to say for sure, but my best guess would be that you have a NULL value in table_A.Column1. if you did have a null values, your query would be equivalent to something like:

SELECT  Column1
FROM    Table_B
WHERE   Column1 NOT IN (1, 2, 3, NULL);

Which is equivalent of:

SELECT  Column1
FROM    Table_B
WHERE   Column1 <> 1
AND     Column1 <> 2
AND     Column1 <> 3
AND     Column1 <> NULL;

Since Column1 <> NULL is not true, the query returns no results. The most syntactically similar way to achieve the desired result where you might have NULL columns is using NOT EXISTS:

INSERT INTO Table_A(column1)
SELECT  Column1
FROM    Table_B AS B
WHERE   NOT EXISTS (SELECT 1 FROM Table_A AS A WHERE A.Column1 = B.Column1);

However, another method you could use is:

INSERT INTO Table_A(column1)
SELECT  Column1
FROM    Table_B AS B
        LEFT JOIN Table_A AS A
            ON A.Column1 = B.Column1
WHERE   A.Column1 IS NULL;

In this by left joining to table_A then stating that A.Column1 has to be NULL, you are removing any records that already exist in Table_A.

I prefer the former (NOT EXISTS), because I think the intent is much more clear, but if you use MySQL the latter will perform better

Or you could also use:

INSERT INTO Table_A(column1)
SELECT  Column1
FROM    Table_B AS B
WHERE   B.Column1 IS NOT NULL
AND     B.COlumn1 NOT IN (SELECT A.Column1 FROM Table_A AS A WHERE A.Column1 IS NOT NULL);
Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123
0

What about using a MERGE statement?

MERGE INTO TABLE_A a
USING TABLE_B b ON (a.column1=b.column1)
WHEN NOT MATCHED THEN
    INSERT (column1) VALUES (b.column1);
Justin
  • 1
  • 1