SQL插入查询

Question

我在处理这段SQL时遇到了困难，想知道是否有人能帮助我。

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

现在，rec_2和rec_3已经清晰并具有绝对值。Rec_1填充了来自另一个表的值。现在我想要插入来自另一张表的尚不存在于此表中的值。我猜想应该使用WHERE NOT IN？因此，它可能类似于这样：

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
)

但是，如何在我的查询中插入rec_1中的这些值呢？

- Matheno

---

'NOT IN' 而不是不存在 - Kiril Rusev

Answer 1

如果rec_2和rec_3是绝对值，请考虑使用简单的INSERT/SELECT。

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)

- Mike Perrenoud

Answer 2

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)

你可能需要查看这个答案以获取更多用法信息

更多细节请点击这里

- Aatish Sai

Answer 3

试试这个

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)

- Mad Dog Tannen

Answer 4

     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

- RameezAli