In this tutorial, you will learn the following topics
  • Create a table using T-SQL
  • Alter a table using T-SQL
  • Drop a table using T-SQL
  • Create a new table from the existing table using T-SQL

Create a table using T-SQL

Use the below syntax to create a table

CREATE TABLE tableName
( 
  columnName1 datatype [NULL | NOT NULL],
  columnName2 datatype [NULL | NOT NULL],
  columnName3 datatype [NULL | NOT NULL],
  ...
);
  • The parameter tableName describes the name of the table which you are going to create.
  • The parameters columnName1, columnName2, columnName3… describe the column names of the table.
  • A column should be denoted as either NULL or NOT NULL. If you don't denote then MS SQL Server will take column value as NULL by default.

Example

(1). Select the database in which you want to create a table

(2). Write the below query to create a table with the "TblStudent" name 

  CREATE TABLE TblStudent
     (
            SRNo VARCHAR(15),
            StudentName VARCHAR(50),
            FatherName VARCHAR(50),
            CourseCode VARCHAR(15)
     )
 (3) Click on the "Execute" button shown in the below image or hit "F5" key from keeboard


Alter a table using T-SQL

ALTER TABLE Statement is used to add, modify or delete columns in the existing table. It is also used to add or drop the various constraints on an existing table.

(1). Use the given syntax to add a column in an existing table

ALTER TABLE tableName 
ADD columnName datatype;
Example: Below SQL Query will add "Address" Column in "TblStudent" table. 

ALTER TABLE TblStudent
ADD Address varchar(255);
(2). Use the given syntax to ALTER/MODIFY a column datatype or size in an existing table 

ALTER TABLE tableName 
ALTER COLUMN columnName datatype;
Example: Below SQL Query will ALTER/MODIFY "SRNo" Column from VARCHAR to INTEGER datatype. 

ALTER TABLE TblStudent
ALTER COLUMN SRNo INT;

(3). Use the given syntax to drop(delete) a column in an existing table 

ALTER TABLE tableName 
DROP COLUMN columnName
Example: Below SQL Query will drop(delete) "Address" Column from "TblStudent" table. 

ALTER TABLE TblStudent
DROP COLUMN Address

Drop table using T-SQL

Use the given syntax to drop a table

DROP TABLE tableName
Example: Below SQL Query will drop "TblStudent" table. 

DROP TABLE TblStudent

Create a new table from the existing table using T-SQL

Use the below syntax to create a new table from an existing table  

SELECT (columnName1, columnName2 …) INTO <NewTableName> FROM <OldTableName>;    
Example: Use the below syntax to create a new table from an existing table  

SELECT  SRNo,StudentName,FatherName,CourseCode INTO TblStudentBackup FROM TblStudent;