How to create a table from the results of a SQL query?

Question

How can I put the results of a query into a table which is NOT created?

For example, put the following query results into a new table EmployeeDetail which IS NOT CREATED. Create the table and put the results at the same time.

select a.Name, b.Id
from Database1 a left join 
     Database2 b
ON a.Id = b.Id

How can this be done?

Answer 1

This will be RDBMS dependant. If you are using SQL Server you can use SELECT ... INTO

select a.Name, b.Id
into EmployeeDetail 
 from Database1 a left join Database2 b ON a.Id = b.Id

Answer 2

You didn't specify your RDBMS system, but in SQL Server it would be like this

select a.Name, b.Id into EmployeeDetail 
from Database1 a left join Database2 b ON a.Id = b.Id

Answer 3

Send the create table command before the sql to insert the rows.

Answer 4

You can use:

CREATE TABLE table_name AS ...

where the ... is your query.

Here is a link with more documentation: http://developer.postgresql.org/pgdocs/postgres/sql-createtableas.html

Answer 5

SELECT INTO

SELECT a.Name, b.id
INTO newTable
FROM from Database1 a 
LEFT JOIN Database2 b

Answer 6

For Oracle SQL*Plus, the following syntax is used

CREATE TABLE <table name> AS <your query>;

For example,

CREATE TABLE managers AS SELECT * FROM employees WHERE desg = 'MANAGER';