Using IF ELSE statement based on Count to execute different Insert statements
SqlSql ServerSql Server-2008TsqlSql Server-2008-R2Sql Problem Overview
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]
)
Sql Solutions
Solution 1 - Sql
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 - Sql
Simply use the following:
IF((SELECT count(*) FROM table)=0)
BEGIN
....
END
Solution 3 - Sql
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 - Sql
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 - Sql
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
Solution 6 - Sql
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.
Solution 7 - Sql
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
Solution 8 - Sql
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 >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 > 0
-- INSERT STATEMENT 1 GOES HERE
ELSE
-- INSERT STATEMENT 2 GOES HERE