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: https://stackoverflow.com/help/how-to-ask — Renato, Mar 20 '20 at 07: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, Mar 20 '20 at 08:10

score 1 · Answer 1 · answered Mar 20 '20 at 08:08

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 '20 at 08:08

Thorsten Kettner

89,309
7
49
73

Actually i want to have multiple insert staements using if else or case when – Omprakash Reddy Mar 20 '20 at 08:23
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. – Thorsten Kettner Mar 20 '20 at 08:32
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. – Omprakash Reddy Mar 20 '20 at 16:04
In SQL Server you can use a stored procedure where you loop over your query results (a cursor loop): https://stackoverflow.com/questions/11852782/t-sql-loop-over-query-results – Thorsten Kettner Mar 20 '20 at 21:51

If else in SQL Server with multiple insert statements

1 Answers1