只插入临时表中缺少的记录

Question

我如何以这种方式执行INSERT，但只插入在表Cities中找不到的记录?

CREATE TABLE #tCities (IDCity VARCHAR(10), NameCity VARCHAR(60))
INSERT INTO #tCities (IDCity, NameCity)
VALUES ('1','New York')
INSERT INTO #tCities (IDCity, NameCity)
VALUES ('2','Boston')

INSERT INTO Cities (ID_City, Name_City)
SELECT IDCity, NameCity
FROM #tCities A
LEFT JOIN Cities B ON A.IDCity = B.ID_City
WHERE A.IDCity IS NULL
DROP TABLE #tCities

Answer 1

可以使用NOT EXISTS代替join，例如

INSERT INTO Cities (ID_City, Name_City)
SELECT IDCity, NameCity
FROM #tCities A
WHERE NOT EXISTS (SELECT 1 from Cities C WHERE C.ID_City = A.IDCity);

Answer 2

你很接近了。如果你使用LEFT JOIN，你首先需要#tCities。你想让WHERE查看Cities是否匹配:

INSERT INTO Cities (ID_City, Name_City)
SELECT t.IDCity,t. NameCity
FROM #tCities t LEFT JOIN
Cities c
ON c.IDCity = t.ID_City
WHERE c.IDCity IS NULL;

Answer 3

将WHERE A.IDCity IS NULL替换为WHERE B.ID_City IS NULL，就可以了