If else in SQL Server with multiple insert statements

Question

select 
    din, driv_height, city, Driver_Addres_Posta_Code, DRIV_GENDER_ID 
from 
    driv_testing 

if DRIV_GENDER_ID IN ('M', 'F') 
begin
    insert into driv_success
end
else DRIV_GENDER_ID not in ('M', 'F')
begin 
    insert into error_logs (error_description) 
    values ('Gender fails')
end

I have one source table driv_testing and sink table driv_success and error_logs table to log bad records.

If DRIV_GENDER_ID IN ('M', 'F') then I want to insert into driv_success otherwise insert into error_logs table..

But I am getting error for the above query.

Hi, welcome to stackoverflow! Your question is not well asked, please read this: stackoverflow.com/help/how-to-ask — Renato
– Renato, Commented Mar 20, 2020 at 7:49
Please tag your request with the DBMS you are using (MySQL, SQL Server, Oracle, ...). In order to answer SQL questions, it is usually necessary to know the DBMS. — Thorsten Kettner
– Thorsten Kettner, Commented Mar 20, 2020 at 8:10

Thorsten Kettner · Accepted Answer · 2020-03-20 08:08:58Z

1

You haven't tagged your DBMS and I don't recognize the syntax you are trying to apply. But generally inserts into different tables require separate statements. Something along the lines of:

insert into driv_success (din)
select din
from driv_testing
where driv_gender_id in ('M', 'F');

insert into error_logs (din, error_description) 
select din, 'Gender fails'
from driv_testing
where driv_gender_id not in ('M', 'F');

answered Mar 20, 2020 at 8:08

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

4 Comments

Omprakash Reddy Over a year ago

Actually i want to have multiple insert staements using if else or case when

Thorsten Kettner Over a year ago

You are selecting all rows of table driv_testing and then you ask if DRIV_GENDER_ID IN('M','F'). Which row would this condition be referring to? You would have to loop through your driv_testing rows instead (if your DBMS provides this - you still haven't told us which DBMS you are using). But this would probably be much slower then my approach with just two insert statements.

Omprakash Reddy Over a year ago

My requirement is in azure data factory copy activity I want to move source data to two tables if record is good(pass all condition like gender must be M or F and postal code should follow particular alphanumeric format) then those records should enter into driv_success table and if record fails due to any condition mismatch it should go into error_log table and it should happen at a time.So I wanted to use if else query statement in copy activity source side instead of giving dataset pointing to a particular table.

Thorsten Kettner Over a year ago

In SQL Server you can use a stored procedure where you loop over your query results (a cursor loop): stackoverflow.com/questions/11852782/…

Collectives™ on Stack Overflow

If else in SQL Server with multiple insert statements

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related