SQL query, store result of SELECT in local variable
I create a query with some results reused. I search a way to put the result into a variable and use it.
A simple way to see what I want something looking like this - I want this:
DECLARE @result1 ?????
SET @result1 = SELECT a,b,c FROM table1
SELECT a AS val FROM @result1
UNION
SELECT b AS val FROM @result1
UNION
SELECT c AS val FROM @result1
Not this :
SELECT a AS val FROM (SELECT a,b,c FROM table1)
UNION
SELECT b AS val FROM (SELECT a,b,c FROM table1)
UNION
SELECT c AS val FROM (SELECT a,b,c FROM table1)
It's not the result of this query that I'm concerned with, but instead:
to stop selecting the result so many times - in my sample, I reselected the table 3 times
the query of
@result1
is usually so much more complex. So, with a variable, the code will be cleaner.
Maybe I want to much - or there's a type of local variable. Or using the type table and set data inside.
What do you suggest me?
Thank you
Solution 1:
You can create table variables:
DECLARE @result1 TABLE (a INT, b INT, c INT)
INSERT INTO @result1
SELECT a, b, c
FROM table1
SELECT a AS val FROM @result1
UNION
SELECT b AS val FROM @result1
UNION
SELECT c AS val FROM @result1
This should be fine for what you need.
Solution 2:
Here are some other approaches you can take.
1. CTE with union:
;WITH cte AS (SELECT a, b, c FROM table1)
SELECT a AS val FROM cte
UNION SELECT b AS val FROM cte
UNION SELECT c AS val FROM cte;
2. CTE with unpivot:
;WITH cte AS (SELECT a, b, c FROM table1)
SELECT DISTINCT val
FROM cte
UNPIVOT (val FOR col IN (a, b, c)) u;
Solution 3:
Isn't this a much simpler solution, if I correctly understand the question, of course.
I want to load email addresses that are in a table called "spam" into a variable.
select email from spam
produces the following list, say:
.accountant
.bid
.buiilldanything.com
.club
.cn
.cricket
.date
.download
.eu
To load into the variable @list:
declare @list as varchar(8000)
set @list += @list (select email from spam)
@list may now be INSERTed into a table, etc.
I hope this helps.
To use it for a .csv file or in VB, spike the code:
declare @list as varchar(8000)
set @list += @list (select '"'+email+',"' from spam)
print @list
and it produces ready-made code to use elsewhere:
".accountant,"
".bid,"
".buiilldanything.com,"
".club,"
".cn,"
".cricket,"
".date,"
".download,"
".eu,"
One can be very creative.
Thanks
Nico