insert values into a table if the value does not exist-CodePudding

I am trying to insert values into a table where if the values does not exist then it should insert them but I am having issue with this SQL.

 insert into table1 (id, name, value)
    select Id, Name, Value
    from table2
    where NOT EXISTS typeId = TypeId;

CodePudding user response：

Should've been something like this:

INSERT INTO table1 (id, name, VALUE)
   SELECT id, name, VALUE
     FROM table2 b
    WHERE NOT EXISTS
             (SELECT NULL
                FROM table1 a
               WHERE a.typeid = b.typeid);

CodePudding user response：

This is what a MERGE statement is good at:

MERGE INTO table1 dst
USING table2 src
ON (src.typeID = dst.typeID)
WHEN NOT MATCHED THEN
  INSERT (id, name, value) VALUES (src.Id, src.Name, src.Value);

Which, for the sample data:

CREATE TABLE table1 (id PRIMARY KEY, typeid, name, value) AS
SELECT 2, 2, 't1-name2', 2 FROM DUAL UNION ALL
SELECT 4, 4, 't1-name4', 4 FROM DUAL;

CREATE TABLE table2 (id PRIMARY KEY, typeid, name, value) AS
SELECT 1, 1, 't2-name1', 1 FROM DUAL UNION ALL
SELECT 2, 2, 't2-name2', 2 FROM DUAL UNION ALL
SELECT 3, 3, 't2-name3', 3 FROM DUAL UNION ALL
SELECT 4, 4, 't2-name4', 4 FROM DUAL UNION ALL
SELECT 5, 5, 't2-name5', 5 FROM DUAL;

Then, after the MERGE:

SELECT * FROM table1;

Outputs:

ID TYPEID NAME VALUE

2 2 t1-name2 2

4 4 t1-name4 4

3 t2-name3 3

5 t2-name5 5

1 t2-name1 1

ID	TYPEID	NAME	VALUE
2	2	t1-name2	2
4	4	t1-name4	4
3		t2-name3	3
5		t2-name5	5
1		t2-name1	1

(Note: you probably want to include TYPEID in the columns you insert.)

db<>fiddle here