Scribd
Upload
23 views3 pages

SQL Create Table Methods

The document outlines various methods to create tables in SQL, including standard creation, creating from existing tables, copying structures, and adding constraints. It also discusses temporary tables, default values, and creating tables in specific schemas or databases. Each method is accompanied by a brief description and an example SQL statement for clarity.

Uploaded by

Sukanya Roy Choudhury
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
23 views3 pages

SQL Create Table Methods

The document outlines various methods to create tables in SQL, including standard creation, creating from existing tables, copying structures, and adding constraints. It also discusses temporary tables, default values, and creating tables in specific schemas or databases. Each method is accompanied by a brief description and an example SQL statement for clarity.

Uploaded by

Sukanya Roy Choudhury
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Different Ways to Create a Table in SQL

1. Standard CREATE TABLE


Defines a new table with column names and data types.

CREATE TABLE Employees (


ID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
HireDate DATE
);

2. CREATE TABLE AS SELECT (CTAS)


Creates a new table from an existing table or query result (structure + data or only
structure).

-- With data
CREATE TABLE NewEmployees AS
SELECT ID, Name FROM Employees WHERE Department = 'Sales';

-- Only structure (PostgreSQL)


CREATE TABLE NewStructureOnly AS
SELECT * FROM Employees WHERE 1=0;

3. CREATE TABLE Using LIKE


Copies the structure of an existing table (no data).

CREATE TABLE EmployeeBackup LIKE Employees;

4. CREATE TABLE with Constraints


Add constraints like PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY.

CREATE TABLE Projects (


ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
Budget DECIMAL(10, 2) CHECK (Budget > 0),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
);

5. CREATE TEMPORARY TABLE


Creates a table that exists only during the session or transaction.

CREATE TEMPORARY TABLE TempSales (


SaleID INT,
Amount DECIMAL(10, 2)
);

6. CREATE TABLE with Default Values


Assigns default values for columns when no value is inserted.

CREATE TABLE Products (


ProductID INT,
Name VARCHAR(50),
Quantity INT DEFAULT 0
);

7. CREATE TABLE Using SELECT INTO (SQL Server)


Creates and populates a new table in one step.

SELECT ID, Name INTO EmployeeCopy


FROM Employees
WHERE Department = 'HR';
8. CREATE TABLE in a Different Schema or Database
You can create tables under specific schemas or even databases.

CREATE TABLE Sales.Orders (


OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);

-- SQL Server (cross-database)


CREATE TABLE OtherDB.dbo.MyTable (
Col1 INT
);

Summary Table
Method Description Example
Standard CREATE TABLE Create with column CREATE TABLE Employees
definitions (...)
CREATE TABLE AS SELECT Create from existing data CREATE TABLE NewTable
AS SELECT ...
CREATE TABLE LIKE Copy structure only CREATE TABLE Copy LIKE
Original;
With Constraints Includes keys and checks PRIMARY KEY, FOREIGN
KEY, CHECK
Temporary Table Exists temporarily CREATE TEMPORARY
TABLE TempTable (...)
With Defaults Assign default values Quantity INT DEFAULT 0
SELECT INTO One-step creation + data SELECT ... INTO NewTable
Schema/DB Specific Create under schema/db CREATE TABLE
Schema.TableName (...)

You might also like