How To Create Table As Select In Sql Server

Problem:

You would like to create a new table in a database with data defined by an SQL query.

Example:

We would like to create the table gamer based on an SQL query. In this query, we select data from another table named championship presented below.

id	gamer	score	championship_date
1	alice	14	2020-08-10
2	supervisor	10	2020-09-28
3	happyman	0	2020-08-10
4	lukas	6	2020-08-10
5	oli	12	2020-08-10
6	biggamer	7	2020-09-12

In the database, let's create a new table named gamer which will store data in all of the columns defined in the table championship (id, gamer, score, and championship_date).

Solution 1:

CREATE TABLE gamer  AS   SELECT   * FROM championship;

Discussion:

If you would like to create a new table, the first step is to use the CREATE TABLE clause and the name of the new table (in our example: gamer ). Then, use the AS keyword and provide a SELECT statement that selects data for the new table. In our example, we selected all columns from the table championship by using the asterisk (*). The result set displays all of the records in the table championship .

However, if you want to create a table with only a subset of the records, you can specify the selected query like in the example below.

Solution 2:

CREATE TABLE gamer  AS   SELECT   gamer, score, championship_date FROM championship  WHERE championship_date <= 2020-08-10;

Here is the result set:

gamer	score	championship_date
alice	14	2020-08-10
happyman	0	2020-08-10
lukas	6	2020-08-10
oli	12	2020-08-10

The SELECT query retrieves only the records with a championship_date date equal to or older than 2020-08-10 (WHERE championship_date <= 2020-08-10). The new table stores fewer columns than in the previous example (SELECT gamer, score, championship_date) without the column id.

A similar solution to this problem is to use the SELECT INTO clause to create a new table and copy data from another table. Take a look at the code:

Solution 3:

SELECT   gamer, score, championship_date INTO gamer FROM championship  WHERE championship_date <= 2020-08-10;

The resulting table is the same. Note that this construction is not present in the SQL standard. In this SQL command, we first write SELECT, then the list of columns, next the keyword INTO, and finally the name of the new table we want to create. Next, you can add WHERE and other SQL clauses like GROUP BY or HAVING to filter records for the new table.

Subscribe to our newsletter Join our monthly newsletter to be
notified about the latest posts.

Email address