Two ways to fake a SQL table [SQL]

Two ways to fake a SQL table [SQL]

The first way is to create a temporary table and insert values into it.

DECLARE @declareTable TABLE (seq int, name VARCHAR(5) ) ;
INSERT INTO @declareTable (seq,name) values
(1,'one'),
(2,'two'),
(3,'three')

select * from @declareTable as dt;

The second way is to create the values inside the select statement.


select
*
from
(values
(1,'one'),
(2,'two'),
(3,'three')
) as tempTable (seq,name);

This will allow you to join a larger table to a temporary table using multiple values. This could also replace values used in “in”.

Putting it together:

DECLARE @declareTable TABLE (seq int, name VARCHAR(5) ) ;
INSERT INTO @declareTable (seq,name) values
(1,'one'),
(2,'two'),
(3,'three')

select
*
from
(values
(1,'one'),
(2,'two'),
(3,'three')
) as tempTable (seq,name)
join
@declareTable as dt on dt.seq = tempTable.seq