将一个表中特定列的值插入到另一个表的特定列中

Question

我在SQL中有两个表，每个表分别有12列和16列。其中4个在两者中都很常见。

我需要的是，我希望将一个表中这4列的值插入到另一个表的列中。

我尝试了以下方法：

INSERT INTO TABLE2 (IDCOMPANY,
                IDPLANT,
                IDTRAINING,
                IDEMPLOYEE,
                IDGRADE,
                IDDEPARTMENT,
                IDDESIGNATION,
                EMAIL,
                STATUS,
                CREATEDBY,
                CREATEDON)
        VALUES (@IDCOMPANY,
                @IDPLANT,
                @IDTRAINING,
                @IDEMPLOYEE,
        (SELECT IDGRADE,
         IDDEPARTMENT,
         IDDESIGNATION,
         EMPLOYEE_EMAIL 
         FROM TABLE1 
         WHERE EMPLOYEECODE=@EMPLOYEECODE ) ,
                @STATUS,
                @CREATEDBY,
                GETDATE())

其他列值由我的应用程序中的参数传递，但我希望从另一个表中传递四列，即IDGRADE,IDDEPARTMENT,IDDESIGNATION,EMPLOYEE_EMAIL。

这能做到吗？？？？我需要一些帮助。

更新：运行时上面的查询给出以下错误：

Msg 116, Level 16, State 1, Procedure USP_INSERT_HR, Line 219
Only one expression can be specified in the select list when the subquery 
is not introduced with EXISTS.
Msg 109, Level 15, State 1, Procedure USP_INSERT_HR, Line 217
There are more columns in the INSERT statement than values specified in 
the VALUES clause. The number of values in the VALUES clause must match the
number of columns specified in the INSERT statement.

Answer 1

您的代码应该具有这种结构。

INSERT INTO TABLE2
(IDCOMPANY,IDPLANT,IDTRAINING,IDEMPLOYEE,IDGRADE,IDDEPARTMENT,IDDESIGNATION,EMAIL,STATUS,CREATEDBY,CREATEDON)
SELECT @IDCOMPANY,@IDPLANT,@IDTRAINING,@IDEMPLOYEE,IDGRADE,IDDEPARTMENT,IDDESIGNATION,EMPLOYEE_EMAIL,@STATUS,@CREATEDBY,GETDATE())
FROM TABLE1 WHERE EMPLOYEECODE=@EMPLOYEECODE  

Answer 2

EDIT-现在问题有了很大的改进，请尝试以下操作（您可以在select中使用变量，而不会出现问题）。INSERT部分中的11列现在应该与11列值匹配——6列由@Variables提供，4列由SELECT FROM TABLE1提供，1列由GETDATE（）提供

INSERT INTO TABLE2 (IDCOMPANY, IDPLANT, IDTRAINING, IDEMPLOYEE, IDGRADE, IDDEPARTMENT,
                    IDDESIGNATION, EMAIL, STATUS, CREATEDBY, CREATEDON)
    SELECT @IDCOMPANY, @IDPLANT, @IDTRAINING, @IDEMPLOYEE,
           IDGRADE, IDDEPARTMENT, IDDESIGNATION, EMPLOYEE_EMAIL,
           @STATUS, @CREATEDBY, GETDATE()
     FROM TABLE1 
     WHERE EMPLOYEECODE=@EMPLOYEECODE

---结束编辑--

INSERT添加了全新的行，从您的问题中可以看出（尽管有点模糊，所以我只是猜测），您希望将表1中4列的值添加到表2中，这些列位于已经存在的记录中的相同名称列中。

如果以上情况成立，那么您需要使用UPDATE（而不是INSERT）。类似于下面的东西应该接近工作。

UPDATE TABLE2 AS T2
SET T2.IDCOMPANY = T1.IDCOMPANY, T2.IDPLANT = T1.IDPLANT, T2.IDTRAINING = T1.IDTTRAINING,
T2.IDEMPLOYEE = T1.IDEMPLOYEE
FROM TABLE2 JoinTbl
INNER JOIN TABLE1 AS T1
ON JoinTbl.EMPLOYEECODE = T1.EMPLOYEECODE