SQL insert query

Question

I'm struggling with this piece of SQL and I was wondering if someone could help me out.

INSERT INTO table_1(
rec_1, 
rec_2, 
rec_3
) 
VALUES (
val_1, 
val_2, 
val_3
)

Now, rec_2 and rec_3 are clear and have absolute values. Rec_1 is filled with values from another table. Now I want to insert the values from the other table which do not exist already in this table. I was guessing I should use WHERE NOT IN?

So it would be something like this:

INSERT INTO table_1(
rec_1, 
rec_2, 
rec_3
) 
VALUES (
val_1,
val_2, 
val_3
) 
WHERE NOT IN (
SELECT rec FROM table_2
)

But.. How can I insert those values in rec_1 in my query?

'NOT IN' instead of not exists – Kiril Rusev Feb 10 '14 at 13:26 — Kiril Rusev, Feb 10 '14 at 13:26

score 5 · Accepted Answer · answered Feb 10 '14 at 13:27

5

How about a simple INSERT/SELECT if rec_2 and rec_3 are absolute values:

INSERT INTO table_1 (rec_1, rec_2, rec_3)
SELECT val_1, 'val_2', 'val_3'
FROM other_table
WHERE val_1 NOT IN (SELECT rec_1 FROM table_1)

answered Feb 10 '14 at 13:27

Mike Perrenoud

66,820
29
157
232

score 4 · Answer 2 · edited May 23 '17 at 12:00

4

INSERT INTO table_1(rec_1, rec_2, rec_3) 
SELECT val_1, val_2, val_3 FROM dual WHERE NOT EXISTS (SELECT rec FROM table_2)

You might want to check this answer for further usage

Further details here

edited May 23 '17 at 12:00

Community

1
1

answered Feb 10 '14 at 13:32

Aatish Sai

1,647
1
26
41

score 2 · Answer 3 · answered Feb 10 '14 at 13:27

2

Try this

INSERT INTO table_1(rec_1, rec_2, rec_3) 
SELECT  val_1, val_2, val_3 FROM tablename 
WHERE NOT EXISTS (SELECT rec FROM table_2)

answered Feb 10 '14 at 13:27

Mad Dog Tannen

7,129
5
31
55

score 0 · Answer 4 · answered Feb 13 '14 at 12:32

     4 ways to insert record
 1--> {Simple Insertion when table column sequence is known}
        Insert into Table1
        values(1,2,...)

    2--> {Simple insertion mention column}  
        Insert into Table1(col2,col4)
        values(1,2)

    3--> {bulk insertion when num of selected collumns of a table(#table2) are equal to Insertion table(Table1) }   
        Insert into Table1 {Column sequence}
        Select * -- column sequence should be same.
           from #table2

    4--> {bulk insertion when you want to insert only into desired column of a table(table1)}
        Insert into Table1 (Column1,Column2 ....Desired Column from Table1)  
        Select Column1,Column2..desired column from #table2
           from #table2

SQL insert query

4 Answers4