INSERT with Multiple select SQL

Question

I'm using SQL Server 2008. I'm trying to insert data into dv_user_akun table with value that I've selected from another table. Please take a look.

INSERT INTO dv_user_akun (user_id, nik, username, password, kode_tipe, flag) 
    SELECT 
        (SELECT
             CASE
                WHEN right(max(user_id), 8) IS NULL
                  THEN 'USR00000001' 
                  ELSE ('USR-' + RIGHT('0000000' + cast(right(max(user_id), 7) + 1 as nvarchar), 7)) 
             END user_id 
         FROM 
             dv_user_akun) as user_id,
        (SELECT 
             Nip, Nip, '81dc9bdb52d04dc20036dbd8313ed055', PositionCode, 1 
         FROM
             Employee 
         WHERE
             Nip NOT IN (SELECT Nik FROM dv_user_akun))

I get this error

The select list for the INSERT statement contains fewer items than the insert list.
The number of SELECT values must match the number of INSERT columns.

Rich Benner · Accepted Answer · 2016-06-04 09:42:02Z

2

You don't have anything in your select statement. I've changed your subqueries to proper joins rather than the old style ansi joins that you have there. The only problem I still have is that you appear to be using the field Nip to insert into both nik and username fields;

INSERT INTO dv_user_akun (user_id, nik, username, password, kode_tipe, flag)
SELECT 
a.user_id
,b.Nip
,b.Nip
,b.pass
,b.PositionCode
,b.number
FROM (
        SELECT CASE 
                WHEN right(max(user_id), 8) IS NULL
                    THEN 'USR00000001'
                ELSE ('USR-' + RIGHT('0000000' + cast(right(max(user_id), 7) + 1 AS NVARCHAR), 7))
                END user_id
        FROM dv_user_akun
        ) a
CROSS JOIN
(
        SELECT 
            Nip
            ,'81dc9bdb52d04dc20036dbd8313ed055' pass
            ,PositionCode
            ,1 number
        FROM Employee
        WHERE Nip NOT IN (
                SELECT Nik
                FROM dv_user_akun
                )
        ) b

edited Jun 4, 2016 at 9:42

answered Jun 4, 2016 at 9:20

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YVS1102 Over a year ago

thanks it works. you make a little mistake there SELECT ,Nip

Collectives™ on Stack Overflow

INSERT with Multiple select SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related