How to select several hardcoded SQL rows?

Solution 1:

Values keyword can be used as below.

select * from 
(values ('test-a1', 'test-a2'), ('test-b1', 'test-b2'), ('test-c1', 'test-c2')) x(col1, col2)

Solution 2:

The following will work for SQL:

SELECT 'test-a1' AS name1, 'test-a2' AS name2 
UNION ALL 
SELECT 'test-b1', 'test-b2'
UNION ALL 
SELECT 'test-c1', 'test-c2'

Solution 3:

UNION ALL is the best bet. It's faster than UNION and you will have mutually exclusive rows.

Solution 4:

Extending the answer of @openshac for oracle, as the below mentioned code works for oracle:

SELECT 'test-a1' AS name1, 'test-a2' AS name2 from dual 
UNION ALL 
SELECT 'test-b1', 'test-b2' from dual
UNION ALL 
SELECT 'test-c1', 'test-c2' from dual

Solution 5:

You can use a temp table, fill it up with your results and then select from it

create table #tmpAAA (name1 varchar(10), name2 varchar(10))
insert into #tmpAAA (name1, name2) 
values ('test_a', 'test_b'),
       ('test_c', 'test_d'),
       ('test_e', 'test_f'),
       ('test_g', 'test_h'),
       ('test_i', 'test_j');
select * from #tmpAAA;

This will return

name1   name2
==================
test_a  test_b
test_c  test_d
test_e  test_f
test_g  test_h
test_i  test_j