CODEWITHSUNDEEP
sqlsql-server-2008
Tanmay Nehete
Tanmay Nehete

Reputation: 2206

Insert into and select combination is not working

This is my query which I want to insert the value which should be selected from another table: 

insert into payment_details_kohin(installment_no)
 values(
select count(installment_amount)+2  
from kohin_plan.payment_details_insert 
where customer_id='KBP100058'
)

…but its gives me an error:

Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'customer_id', table 'kohinoor_rdfd.kohin_plan.payment_details_kohin'; column does not allow nulls. INSERT fails. The statement has been terminated.

When I try the following query:

insert into payment_details_kohin(installment_no)
values(
select count(installment_amount)+2  
from kohin_plan.payment_details_insert 
where customer_id='KBP100058'
)

…it gives me the following error

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

Upvotes: 3

Views: 2742

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Your problem is that you have a non-NULL customer id. You need to insert it into the table as well:

insert into payment_details_kohin(customer_id, installment_no)
    select customer_id, count(installment_amount)+2  
    from kohin_plan.payment_details_insert 
    where customer_id='KBP100058';

However, when I see such inserts, sometimes what is really wanted is an update:

update payment_details_kohin
    set installment_no = (select count(installment_amount) + 2
                          from kohin_plan.payment_details_insert 
                          where payment_details_kohin.customer_id = payment_details_insert.customer_id
                         )
    where customer_id = 'KBP100058';

Upvotes: 2

Developerzzz
Developerzzz

Reputation: 1126

Dear Friend when you are inserting value in one table from another table or using select for inseting then you dont have to spacity the "Value" Key Word

so simple do the following 

INSERT INTO payment_details_kohin(installment_no)
SELECT count(installment_amount) + 2  
FROM kohin_plan.payment_details_insert 
WHERE customer_id = 'KBP100058'

Upvotes: 1

M.Ali
M.Ali

Reputation: 69574

You dont need the Values clause here just do as follows 

INSERT INTO payment_details_kohin(installment_no)
SELECT ISNULL(COUNT(installment_amount), 0) + 2  
FROM kohin_plan.payment_details_insert 
WHERE customer_id = 'KBP100058'

Upvotes: 2

Related Questions