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(*)>0 THEN 'TRUE' ELSE 'FALSE' END
(
  SELECT [Some Column], COUNT(*) TotalCount
  FROM INCIDENTS
  WHERE [Some Column] = 'Target Data'
  GROUP BY [Some Column]
)

Solution 1

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 > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END 

Solution 2

Simply use the following:

IF((SELECT count(*) FROM table)=0)
BEGIN

....

END

Solution 3

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

Solution 4

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

Solution 5

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 >= 0 and totalCount < 11 then '0-10'
            when tatalCount > 10 and totalCount < 101 then '10-100'
            else '>100' end as newColumn
from (
  SELECT [Some Column], COUNT(*) TotalCount
  FROM INCIDENTS
  WHERE [Some Column] = 'Target Data'
  GROUP BY [Some Column]
) A

View more solutions

Comments

[user1934821]

Hi Jeff, Im not entirely sure what you mean by "reference the COUNT(*) as the Total Count from your nested query" this does not parse correctly

[Jeff]

You have a nested query that selects COUNT(*) and assigns it the column TotalCount. That is a derived table. Check out my last update and see if that works for you.

[Ray K]

Mahmoud Gamal brings up another great (better) way in his comment: If Exists - that way you are not having to create a variable for existing rows, you can work it right into your IF stmt.

[user1934821]

thank you so much! this is exactly what I am looking for! a really simple and elegant solution!

[user1934821]

thanks very much for your input, but 2 separate queries would not suit in this instance.

[skwisgaar]

sorry guys, am I right, that both solutions can be ran only in the procedure, not plain dml (like insert or similar)?