CODEWITHSUNDEEP
sqlsql-servert-sql
Odatas
Odatas

Reputation: 178

When select from insert into returns no values do something different

I want to insert rows into a table. The table is empty when I start. My query is as follows:

Select TOP 1 *
INTO #Result
FROM #SmallTable
WHERE CategoryID=11
ORDER BY ExpValue DESC;

It works flawless. But I want now to account for the case where the this returns no value. But I'm not sure how to approach this.

I could either make a case and select and ask if SELECT TOP 1 returns any values. Or I could check after I insert if there is a value present. But which approach would be better? Or is there an even better one?

Upvotes: 0

Views: 48

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use apply :

select top (1) coalesce(st.CategoryID, 0) as CategoryID, . . 
into #destination
from ( values (11) 
     ) t(CategoryID) left join
     #SmallTable st
     on st.CategoryID = t.CategoryID
order by st.ExpValue desc;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522777

You could use a union trick here to insert a dummy value should the first query not return any records:

INSERT INTO #Result (col)
SELECT TOP 1 col
FROM
(
    SELECT TOP 1 col, 1 AS pos FROM #SmallTable WHERE CategoryID = 11 ORDER BY ExpValue DESC
    UNION ALL
    SELECT 'NA', 2
) t
ORDER BY pos;

Upvotes: 1

Darkwing
Darkwing

Reputation: 7595

Look at @@ROWCOUNT

This returns the number of rows affected by the last procedure. 

IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were inserted';

Upvotes: 1

Related Questions