CODEWITHSUNDEEP
sqlsql-server
user5138680
user5138680

Reputation:

How to insert values into one table from another table

I have two tables i.e., table1 and table2 with same structure and I want to insert all the values of table1 into table2. These table contains two image type column. I have tried this query but couldn't succeed.

DECLARE @imagepan VARBINARY(MAX)
DECLARE @imagecheque VARBINARY(MAX)

SET @imagepan=(SELECT panid FROM table1 WHERE Emp_code='E001')
SET @imagecheque=(SELECT cancel_checqe FROM table1 WHERE Emp_code='E001')
INSERT INTO table2 
    (transno, Emp_code, PanNo, panid, panext, entdt_pan, banknm, 
    cancel_checqe, chqExt, entDt_bnk, Acc_No, IFSC, Cheque_nm, status_pan1, 
    status_pan, status_bnk1, status_bnk, userid, entdt, panModify_by, 
    panModify_on, bnkModify_by, bnkModify_on) 
VALUES
    (SELECT 
        transno, Emp_code, PanNo, @imagepan, panext, entdt_pan, 
        banknm, @imagecheque, chqExt, entDt_bnk, Acc_No, IFSC, Cheque_nm, 
        status_pan1, status_pan, status_bnk1, status_bnk, userid, entdt, 
        panModify_by, panModify_on, bnkModify_by, bnkModify_on 
    FROM 
        table1 
    WHERE 
        Emp_code='E001')

How can I achieve this?

Upvotes: 0

Views: 73

Answers (2)

Prashant Mishra
Prashant Mishra

Reputation: 637

Remove 'values' keyword

insert into table2 (transno, Emp_code, PanNo, panid, panext, entdt_pan, 
                    banknm, cancel_checqe, chqExt, entDt_bnk, Acc_No, 
                    IFSC, Cheque_nm, status_pan1, status_pan, 
                    status_bnk1, status_bnk, userid, entdt, panModify_by, 
                    panModify_on, bnkModify_by, bnkModify_on) 
    select 
        transno, Emp_code, PanNo, @imagepan, panext, entdt_pan, 
        banknm, @imagecheque, chqExt, entDt_bnk, Acc_No, 
        IFSC, Cheque_nm, status_pan1, status_pan, 
        status_bnk1, status_bnk, userid, entdt, panModify_by, 
        panModify_on, bnkModify_by, bnkModify_on 
    from 
        table1 
    where 
        Emp_code = 'E001';

Upvotes: 1

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

Just remove values ( and closing ) at the end:

DECLARE @imagepan VARBINARY(MAX);
DECLARE @imagecheque VARBINARY(MAX);

SET @imagepan = (SELECT panid FROM table1 WHERE Emp_code = 'E001');
SET @imagecheque = (SELECT cancel_checqe FROM table1 WHERE Emp_code = 'E001');

INSERT INTO table2 (transno, Emp_code, PanNo, panid, panext, entdt_pan, 
banknm, cancel_checqe, chqExt, entDt_bnk, Acc_No, IFSC, Cheque_nm, 
status_pan1, status_pan, status_bnk1, status_bnk, userid, entdt, 
panModify_by, panModify_on, bnkModify_by, bnkModify_on) 
SELECT transno, Emp_code, PanNo, @imagepan, panext, entdt_pan, 
banknm, @imagecheque, chqExt, entDt_bnk, Acc_No, IFSC, Cheque_nm, 
status_pan1, status_pan, status_bnk1, status_bnk, userid, entdt, 
panModify_by, panModify_on, bnkModify_by, bnkModify_on 
FROM table1 
WHERE Emp_code='E001';

Upvotes: 0

Related Questions