Insert values into table based on condition, sql s

问题:

I try to insert values into my table in stored procedure based on the conditions:

alter procedure service_report_st2
as
insert into Service_report_step1(appointment_or_house) 
select
case
when Service_report_step1.card_uid in(select card_uid from visits where vidpos_kod = 'A')
then '1'

when Service_report_step1.card_uid in(select card_uid from visits  where vidpos_kod = 'B')
then '2'

else '3'
end

The error is in Service_report_step1.card_uid it is not found, however this is the first column in Service_report_step1 table. appointment_or_house is the second column, which should contain '1', '2' or '3' if visits table contains card_uid in Service_report_step1.

I'd appreciate any help!

Short example:

Visits table has 2 columns: Card_uid and vidpos_kod

card_uid         vidpos_kod
 111-111              A
 222-222              B
 333-333              A
 444-444              C

now Service_report_step1 table has card_uid (column that contains all possible cards) and appointment_or_house column

card_uid      appointment_or_house
 111-111              1
 222-222              2
 333-333              1
 444-444              1

So, If I have the same card_uids in Service_report_step1 as in Visits I determine appointment_or_house column based on vidpos_kod in Visits (A, C is '1', B is '2')

I need to insert into Service_report_step1 all data correctly as in example.

回答1:

You are using a select statement, but without a FROM clause

You need to specify

INSERT INTO ...
SELECT *
FROM ...

Or something like that.

The other option would be to use Table Value Constructor (Transact-SQL)

回答2:

insert into Service_report_step1(appointment_or_house) 
select
case vidpos_kod
when 'A' then '1'
when 'B' then '2'
when 'C' then '3'
end
from visits 
where vidpos_kod like '[ABC]'