CODEWITHSUNDEEP
sql-server-2008multiple-resultsets
Adam Levitt
Adam Levitt

Reputation: 10476

SQL Server combine result sets

I'm trying to combine the result sets of two different queries into a single result set. Is this possible without using a stored procedure, perhaps using the WITH keyword?

Take the following case:

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

Please ignore the obvious solution of suggesting an OR here as I'm leaving out the complex dynamic constraints just to perhaps illustrate my point.

I'd like to take the results from each of these queries individually and combine them into a single result set.

Upvotes: 3

Views: 2602

Answers (3)

Taryn
Taryn

Reputation: 247720

You can use a UNION ALL between the two queries:

SELECT *, '2013' Source
FROM student 
WHERE graduation_year = 2013 
UNION ALL
SELECT *, '2014' Source
FROM student 
WHERE graduation_year = 2014

See SQL Fiddle with Demo

I added a field called source if you need to identify what dataset the row came from, this can be excluded if not needed.

If you want this in a temp table you can use some like this:

select *
into #temp
from
(
  SELECT *, '2013' Source
  FROM student 
  WHERE graduation_year = 2013 
  UNION ALL
  SELECT *, '2014' Source
  FROM student 
  WHERE graduation_year = 2014 
) src


select * from #temp

See SQL Fiddle with Demo

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can use the UNION ALL

SELECT *
FROM student 
WHERE graduation_year = 2013 
UNION ALL
SELECT *
FROM student 
WHERE graduation_year = 2014

Upvotes: 2

Aaron Bar
Aaron Bar

Reputation: 620

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
Union All -- Retrieves all results from both recordsets including both duplicates
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
Union -- Retrieves all results from both recordsets discarding duplicates
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

Upvotes: 1

Related Questions