I'm trying to dynamically create a variable or table in SQL which will store distinct values as a result for another sql query.
declare sample_table table
( values varchar(100))
insert into @sample_table values (select t1.value from my_tablw as t1 group by t1.value);
Supposing the distinct values in column value can change from table query to another table query, I want to store the result of this query in a user defined variable/table which can be used later in another query.
Depending on your definition of can be used later you can use a local temp table or table variable.... you just need to change the syntax a bit to not use the values since you are inserting from the results of a query. I also used DISTINCT below which is clearer than the GROUP BY without an aggregate function.
declare sample_table table ([values] varchar(100))
insert into @sample_table
select distinct t1.value
from my_tablw as t1
--one way to use it
select *
from newTable
where columnVal in (select * from @sample_table)
--another way to use it
select at.*
from anotherTable at
inner join @sample_table t on
t.column = at.column
--and another way...
select f.*
from finalTable f
where exists (select * from @sample_table t where t.column = f.column)
If you need this to be used outside the scope of your current batch, you'll need to use a persisted table or global temporary table.
distinct feature in sql is less "efficient" than group by so I haven't been using the former much.
mysql-variables? Are you using a MySQL database?