Insert values into table based on condition, sql server

Question

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.

Your SELECT is lacking a FROM clause that defines which table you're selecting your rows from ..... — marc_s
– marc_s, Commented Aug 8, 2013 at 5:33

Adriaan Stander · Accepted Answer · 2013-08-08 05:34:05Z

2

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)

answered Aug 8, 2013 at 5:34

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Comments

podiluska · Accepted Answer · 2013-08-08 08:03:45Z

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]'

answered Aug 8, 2013 at 8:03

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

Insert values into table based on condition, sql server

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related