SQL Server: create table

create table … as select … from

Apparently, SQL Server doesn't allow to create a table from a select statement with the syntax that I am used from Oracle.

create table tq84_new_table as
select
  *
from
  tq84_old_table
where
  …

Instead, the new table can be created with

select *
  into tq84_new_table
  from tq84_old_table
where
  …

Temporary tables

A temporary table is created by prepending its name with a #:

create table #tq84 (
  col1 int,
  col2 varchar(10)
);

A temporary table is automatically dropped when the connection that created it terminates.

A global temporary table is created by prepending its name with ##:

create table ##tq85 (
  col1 int,
  col2 varchar(10)
);

A global temporary table is automatically dropped when the connection that craeated it terminates and the last query of potential other connections are finished using it.

Temporary tables are stored in the tempdb database.

SQL Server: create table

create table … as select … from

Temporary tables

See also