Insert values of specific columns from one table to specific columns in another table

Question

I have two table in SQL, each having 12 and 16 columns respectively.Out of these 4 are common in both.

What I need is that I want values of these 4 columns from one table to be inserted to the columns in another table.

I tried the following:

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())

Other column values are passed by parameters from my application but the four columns i.e IDGRADE,IDDEPARTMENT,IDDESIGNATION,EMPLOYEE_EMAIL I want from another table.

Can this be done???? I need some help here.

Update: The above query when run gives the following error:

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.

score 1 · Answer 1 · answered Oct 01 '14 at 07:24

Your code should have this structure.

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

Dave Brown · Answer 2 · 2014-10-01T09:57:30.650

EDIT - Now the question is much improved, try the following (you can use variables inside a select without a problem). The 11 Columns in the INSERT part should now match with 11 Columns of Values - 6 Provided by @ Variables, 4 provided by the SELECT FROM TABLE1 and 1 from 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

---END EDIT---

INSERT adds brand new rows, and it appears (although slightly ambiguous so I'm only guessing) from your question that you want the values of the 4 columns in table1 to be added to table2 in the same named columns in records that already exist there.

If the above is true, then you need to use UPDATE (and not INSERT). Something similar to below should come close to working.

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

Insert values of specific columns from one table to specific columns in another table

2 Answers2