SQL SERVER多列子查询

今天，写导入数据的存储过程时，用到多列子查询，想把临时表里有的，而原表里没有的数据插入到原表里面。原表里若有了，就不用再插入了。

我当时这样写的：

USE SUBJECTDB_DATA;
   BEGIN TRAN;
   INSERT INTO SUBJECTDB.ProDefine.Project_Corporation
   SELECT pro.CODE,COR.CODE
   FROM BDD_PROJECTINFO_INSERT INS
   LEFT JOIN SUBJECTDB.PRODEFINE.Corporation COR ON INS.所属法人公司=COR.CNNAME
   LEFT JOIN SubjectDB.ProDefine.ProjectInfo pro ON INS.项目名称=PRO.CNNAME
   WHERE (PRO.CODE,cor.code) NOT IN (SELECT (PROJECTCODE,CorperationCode) FROM SUBJECTDB.ProDefine.Project_Corporation)

结果报错：

消息 4145，级别 15，状态 1，第 8 行
An expression of non-boolean type specified in a context where a condition is expected, near ','.
消息 102，级别 15，状态 1，第 8 行
Incorrect syntax near ','.
出错原因：oracle里面多列子查询可以这样写WHERE(col1,col2) NOT IN(SELECT (COL1,COL2) FROM ……），但SQL SERVER里面不支持。

解决办法：用CHECKSUM模拟。

如，改动如下，就可以了。

USE SUBJECTDB_DATA;
   BEGIN TRAN;
   INSERT INTO SUBJECTDB.ProDefine.Project_Corporation
   SELECT pro.CODE,COR.CODE
   FROM BDD_PROJECTINFO_INSERT INS
   LEFT JOIN SUBJECTDB.PRODEFINE.Corporation COR ON INS.所属法人公司=COR.CNNAME
   LEFT JOIN SubjectDB.ProDefine.ProjectInfo pro ON INS.项目名称=PRO.CNNAME
   where  checksum(PRO.CODE,COR.CODE) not in  (select checksum(PROJECTCODE,CorperationCode) FROM SUBJECTDB.ProDefine.Project_Corporation)

 还可以通过NOT EXISTS实现：

如：

USE SUBJECTDB_DATA;
   BEGIN TRAN;
   INSERT INTO SUBJECTDB.ProDefine.Project_Corporation
   SELECT pro.CODE,COR.CODE
   FROM BDD_PROJECTINFO_INSERT INS
   LEFT JOIN SUBJECTDB.PRODEFINE.Corporation COR ON INS.所属法人公司=COR.CNNAME
   LEFT JOIN SubjectDB.ProDefine.ProjectInfo pro ON INS.项目名称=PRO.CNNAME

  WHERE NOT EXISTS(SELECT *

                                                       FROM  SUBJECTDB.ProDefine.Project_Corporation P_COR

                                 WHERE P_COR.PROJECTCODE=PRO.CODE

                                             AND P_COR.CorperationCode=COR.CODE

                                                          )