Using IF ELSE statement based on Count to execute different Insert statements

Question

While I am searching through my database  I run an INSERT statement if I find that a particular item does not exist  and I run a different INSERT statement if I find one or more of this item   I am not entirely sure how to use the IF ELSE expressions   What I have so far is a statement that will count the number of times the target data appears  it will print TRUE if it is greater than 0  if not  it will print FALSE  I can t find any examples to help me understand how I can use this to run two different INSERT statements   Here is what I have so far   SELECT CASE WHEN COUNT    gt 0 THEN  TRUE  ELSE  FALSE  END     SELECT  Some Column   COUNT    TotalCount   FROM INCIDENTS   WHERE  Some Column     Target Data    GROUP BY  Some Column

User · Answer

one obvious solution is to run 2 separate queries, first select all items that have count=1 and run your insert, then select the items with count>1 and run the second insert.

as a second step if the two inserts are similar you can probably combine them into one query.

another possibility is to use a cursor to loop thru your recordset and do whatever logic you need for each line.

User · Answer

Depending on your needs  here are a couple of ways   IF EXISTS  SELECT   FROM TABLE WHERE COLUMN    SOME VALUE       --INSERT SOMETHING ELSE     --INSERT SOMETHING ELSE   Or a bit longer  DECLARE  retVal int  SELECT  retVal   COUNT     FROM TABLE WHERE COLUMN    Some Value   IF   retVal  gt  0  BEGIN     --INSERT SOMETHING END ELSE BEGIN     --INSERT SOMETHING ELSE END

User · Answer

As long as you need to find it based on Count just more than 0  it is better to use EXISTS like this   IF EXISTS  SELECT 1 FROM INCIDENTS  WHERE  Some Column     Target Data   BEGIN     -- TRUE Procedure END ELSE BEGIN     -- FALSE Procedure END

User · Answer

Not very clear what you mean by       I cant find any examples to help me understand how I can use this to   run 2 different statements       Is it using CASE like a SWITCH you are after    select case when totalCount  gt   0 and totalCount  lt  11 then  0-10              when tatalCount  gt  10 and totalCount  lt  101 then  10-100              else   gt 100  end as newColumn from     SELECT  Some Column   COUNT    TotalCount   FROM INCIDENTS   WHERE  Some Column     Target Data    GROUP BY  Some Column    A

User · Answer

There are many  many ways to code this  but here is one possible way   I m assuming MS SQL  We ll start by getting row count  Another Quick Example  and then do if else  -- Let s get our row count and assign it to a var that will be used --    in our if stmt  DECLARE  HasExistingRows int -- I m assuming it can fit into an int SELECT  HasExistingRows   Count        ELSE 0 -- false FROM    INCIDENTS WHERE  Your Criteria  GROUP BY  Required Grouping    Now we can do the If   Else Logic  MSDN Docs  -- IF   Else   Begin   END Syntax IF  HasExistingRows   0 -- No Existing Rows    BEGIN        Insert Logic for No Existing Rows     END ELSE -- existing rows are found    BEGIN        Insert logic for existing rows     END   Another faster way  inspired by Mahmoud Gamal s comment    Forget the whole variable creation   assignment - look up  EXISTS  - MSDN Docs 2   IF EXISTS   SELECT Query      BEGIN        INSERT Version 1     END ELSE    BEGIN        INSERT version 2     END

User · Answer

IF exists  IF exists  select   from table 1 where col1    value   BEGIN     -- one or more     insert into table 1  col1  values   valueB   END ELSE     -- zero     insert into table 1  col1  values   value

User · Answer

Simply use the following   IF  SELECT count    FROM table  0  BEGIN        END

User · Answer

If this is in SQL Server  your syntax is correct  however  you need to reference the COUNT    as the Total Count from your nested query   This should give you what you need   SELECT CASE WHEN TotalCount  gt 0 THEN  TRUE  ELSE  FALSE  END FROM     SELECT  Some Column   COUNT    TotalCount   FROM INCIDENTS   WHERE  Some Column     Target Data    GROUP BY  Some Column    DerivedTable   Using this  you could assign TotalCount to a variable and then use an IF ELSE statement to execute your INSERT statements   DECLARE  TotalCount int SELECT  TotalCount   TotalCount FROM     SELECT  Some Column   COUNT    TotalCount   FROM INCIDENTS   WHERE  Some Column     Target Data    GROUP BY  Some Column    DerivedTable IF  TotalCount  gt  0     -- INSERT STATEMENT 1 GOES HERE ELSE     -- INSERT STATEMENT 2 GOES HERE

[sql] Using IF ELSE statement based on Count to execute different Insert statements

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to sql-server-2008-r2