T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - CREATE TABLE



The T-SQL (Transact-SQL) CREATE TABLE statement is used to create a new table. Creating a table involves providing a name to the table and defining name and data type (e.g. varchar, integer, date, etc.) of each column.

Syntax

The syntax of using CREATE TABLE statement in T-SQL (Transact-SQL) is given below:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
  .....
);

Example: Create a table

The mentioned below statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

In the above statement, the data type specifies what type of data the column can hold. After the data type, optional attributes can be provided for each column, like NOT NULL is used to ensure that a column cannot have a NULL value. See T-SQL Constraints for more information.

The above statement will create a empty table named Employee containing five columns. When the table is created successfully it displays a message. Along with this, it can also be checked using EXEC sp_columns command as shown below:

EXEC sp_columns Employee; 

This result of the above code will be similar to:

TABLE_OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPETYPE_NAMEPRECISIONLENGTH
dboEmployeeEmpID4int identity104
dboEmployeeName12varchar255255
dboEmployeeCity12varchar100100
dboEmployeeAge4int104
dboEmployeeSalary3decimal1820

This indicates that the Employee is now available in the database which can be used to store information related to employee.

Create a table using Another table

The T-SQL (Transact-SQL) SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns. When creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT statement).

Syntax

The syntax for using SELECT INTO statement in T-SQL (Transact-SQL) is given below:

/* copy all columns into new_table */
SELECT *
INTO new_table
FROM old_table
WHERE condition;

/* copy selected columns into new_table */
SELECT column1, column2, column3, ...
INTO new_table
FROM old_table
WHERE condition;

Example:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • Copy all columns of a table: The below statement is used to copy all columns of Employee table into a new table called Employee_PT. The new table will contain all records of Employee table where the age of the employee is less than 28.

    SELECT * INTO Employee_PT 
    FROM Employee
    WHERE Age < 28;
    
    -- see the result
    SELECT * FROM Employee_PT;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
  • Copy selected columns of a table: It is also possible to copy selected columns when using SELECT INTO statement. In the example below, the columns which are copied are: EmpID, Name and City.

    SELECT EmpID, Name, City INTO Employee_PT 
    FROM Employee
    WHERE Age < 28;
    
    -- see the result
    SELECT * FROM Employee_PT;
    

    This will produce the result as shown below:

    EmpIDNameCity
    1JohnLondon
    2MarryNew York
    3JoParis
  • Using AS clause: The AS clause can be used to rename the column name. See the example below:

    SELECT EmpID, Name AS EmployeeName, City INTO Employee_PT 
    FROM Employee
    WHERE Age < 28;
    
    -- see the result
    SELECT * FROM Employee_PT;
    

    This will produce the result as shown below:

    EmpIDEmployeeNameCity
    1JohnLondon
    2MarryNew York
    3JoParis