sql - 如何将目标表中尚不存在的行从另一个表插入到一个表中？

Question

我想将 Table2 中的行复制到 Table1 中，只要它们在 Table1 中不存在。现在我得到一般错误Incorrect syntax near the keyword 'WHERE'.和Incorrect syntax near the keyword 'NOT'.

 DECLARE @UserID varchar(100)
 SET @UserID = 'me'

 INSERT INTO Table1 (Col1, Col2, Col3, Col4, Col7,
                        Col8, Col9, Col5, Col6)
            (SELECT 2 AS [Col1], Col2, Col3, Col4, Col7,
                        Col8s, GETDATE() AS [Col9],  @UserID AS [Col5], GETDATE() AS [Col6]
            FROM Table2 AS bpt)
            WHERE (SELECT (CONVERT(varchar(10), Col2) + Col3 + Col4) FROM Table2) NOT EXISTS 
(SELECT (CONVERT(varchar(10), Col2) + Col3 + Col4) FROM Table1)

标签： sqlsql-server

Answer 1

您可以使用以下查询来创建选择语句以插入表 1。

SELECT col1,col2
FROM table2
WHERE NOT EXISTS (SELECT col1,col2 
                  FROM table1
                  WHERE table1.pk = table2.pk);

PK---->主键