我有一个Microsoft SQL数据库，我试图插入一些数据。我在4列上有一个唯一的键，我想在检查数据时将多个表中的数据插入到该表中，以确保它不会违反键的唯一性。如果我在一个列上做这个，我会做一个NOT IN，就像这样。
INSERT TABLE_A (FLD_1)
SELECT FLD_1
FROM TBL_B
INNER JOIN TBL_C
ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE TBL_B.FLD_1 NOT IN
(
SELECT TBL_A.FLD_1 FROM TBL_A
)
有什么想法吗？
使用NOT EXISTS代替，因为您必须处理多个列。
http://www.techonthenet.com/sql/exists.php
编辑：
未经测试，但大致是这样的：
SELECT FLD_1
FROM TBL_B
INNER JOIN TBL_C ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS
(
SELECT TBL_A.FLD_1 FROM TBL_A INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1
)
对于多列检查，大致如下：
SELECT FLD_1, FLD_2, FLD_3, FLD_4)
FROM TBL_B
INNER JOIN TBL_C ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS
(
SELECT TBL_A.FLD_1, TBL_A.FLD_2, TBL_A.FLD_3, TBL_A.FLD_4
FROM TBL_A
INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1 AND
TBL_B.FLD2 = TBL_A.FLD2 AND
TBL_B.FLD3 = TBL_A.FLD3 AND
TBL_B.FLD4 = TBL_A.FLD4
)
使用NOT EXISTS代替，因为您必须处理多个列。
http://www.techonthenet.com/sql/exists.php
编辑：
未经测试，但大致是这样的：
SELECT FLD_1
FROM TBL_B
INNER JOIN TBL_C ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS
(
SELECT TBL_A.FLD_1 FROM TBL_A INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1
)
对于多列检查，大致如下：
SELECT FLD_1, FLD_2, FLD_3, FLD_4)
FROM TBL_B
INNER JOIN TBL_C ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS
(
SELECT TBL_A.FLD_1, TBL_A.FLD_2, TBL_A.FLD_3, TBL_A.FLD_4
FROM TBL_A
INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1 AND
TBL_B.FLD2 = TBL_A.FLD2 AND
TBL_B.FLD3 = TBL_A.FLD3 AND
TBL_B.FLD4 = TBL_A.FLD4
)
也可以使用EXCEPT关键字。
SELECT FLD_1, FLD_2, FLD_3, FLD_4)
FROM TBL_B
INNER JOIN TBL_C ON TBL_B.FLD_1 = TBL_C.FLD_1
EXCEPT
SELECT TBL_A.FLD_1, TBL_A.FLD_2, TBL_A.FLD_3, TBL_A.FLD_4
FROM TBL_A
INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1 AND
TBL_B.FLD2 = TBL_A.FLD2 AND
TBL_B.FLD3 = TBL_A.FLD3 AND
TBL_B.FLD4 = TBL_A.FLD4