SQL Server Create Alter and Drop Table

Posted by

We have learnt How to Create SQL Server Database in previous blog –SQL Server Database Operations .  

The blog covers the topic on  how we can create table in SQL Server, alter table in SQL Server and drop table in SQL Server.

CREATE TABLE in our user defined database oracleappshelpDB. Lets consider we need to store the online users for the website – http://oracleappshelp.com

SQL Server Create Table Syntax:

CREATE TABLE tableName
(
Table_col_1 datatype [ NULL | NOT NULL ],
Table_col_2 datatype [ NULL | NOT NULL ],

Table_col_n datatype [ NULL | NOT NULL ]
);

WHERE
Table_col_1, Table_col_2, …Table_col_n depicts the NAMES of the Column in the TABLE
datatype depicts the type of data to be stored in that column
[ NULL | NOT NULL ] depicts of the column value could be NULL or NOT NULL (mandate)

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 							IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
)

Let’s enter the sample data using SQL Server Query. For that we need to use the INSERT SQL Query. Here is the syntax for that

INSERT INTO tableName
(Table_col_1, Table_col_2, …Table_col_n )
VALUES
(Table_col_value_1, Table_col_value_1, … Table_col_value_n),
(Table_col_value_1, Table_col_value_2, … Table_col_value_n),
…;

WHERE

Table_col_value_1 depicts the value for the first column of the table

Table_col_value_2 depicts the value for the second column of the table and so on..

Insert into oracleappshelpUsers values (1, 'Rohit', 'Kumar','Srivastava' );
Insert into oracleappshelpUsers values (2, 'Mohit', '','Sharma' );
Insert into oracleappshelpUsers values (3, 'Rajeev', 'Kumar','Jain' );
Insert into oracleappshelpUsers values (4, 'Snehashish', '','Nayak' );

Create New SQL Server Table from Existing Table

There could be need where you need to create a new SQL Server Table but it should have the same data elements / Columns. The below given SQL syntax can be used

Syntax – Create Table from existing Table

CREATE TABLE new_sql_server_table_name AS
SELECT Table_col_1, Table_col_2,…Table_col_n
FROM existing_table_name;

WHERE
Table_col_1, Table_col_2, …Table_col_n depicts the NAMES of the Column in the existing TABLE

CREATE TABLE oracleappshelpUsers_backup AS
SELECT UserId, GivenName,FamilyName
FROM oracleappshelpUsers;

New Table using SQL Server Management Studio

Please find below the steps for New Table using SQL Server Management Studio.

  1. Connect to Microsoft SQL Server Instance
  2. Expand the Object Explorer and select the Database oracleappshelpDB
  3. Right click on Table -> New -> Table
  4. Enter the Table Column Name , data type value
  5. Select if column is to be allowed as NULL or NOT NULL
  6. Save the table

SQL Server ALTER Table

This is again very common to add New Columns or to change the data type for the columns in the existing table. Below given is the syntax to ALTER the table as per need.

SQL Server ALTER Table – Add Column

Alter Table Syntax :

ALTER TABLE table_name
ADD column_name datatype [ NULL | NOT NULL ];

ALTER TABLE oracleappshelpUsers
ADD  DOB DATE  NOT NULL ;

Lets say if we need to have a column to store the Flag value for the users who are deleted or not . Then below SQL could be used by adding DEFAULT value.

ALTER TABLE oracleappshelpUsers
ADD  DELETED Varchar(1)  NOT NULL  DEFAULT 'N';

SQL Server Alter Table Drop Column

Lets say we added the column NICKNAME in the Table oracleappshelpUsers but it is not required as per the Database finalization .

Syntax – Alter Table Drop Column

ALTER TABLE table_name
DROP COLUMN column_name

SQLAlter Table Drop Column

ALTER TABLE oracleappshelpUsers
DROP COLUMN  NICKNAME

SQL Server Drop Table

Below given is the syntax and SQL Query for dropping the SQL Server Table.

Syntax – SQL Server Drop Table

Drop Table <table_name>

 Drop Table oracleappshelpUsers

SQL Server Truncate Table

Below given is the syntax and SQL Query for truncating the SQL Server Table. This SQL command allows to DELETE the data from the Table but Table remains there.

Syntax – SQL Server Truncate Table

Truncate Table <table_name>

Truncate Table <table_name>

Truncate Table oracleappshelpUsers

Below given is the complete Table with all columns having CONSTRAINTS.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 							IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
    DOB 				DATE 				NOT NULL,
	Address1 			VARCHAR(255) 		NOT NULL,
	Address2 			VARCHAR(255) 		NOT NULL,
	City	 			VARCHAR(100) 		NOT NULL,
	State 	 			VARCHAR(100) 		NOT NULL,
	Pincode				INT							,
	CreatedBy 			VARCHAR(128) 		NOT NULL,
	UpdatedBy 			VARCHAR(128) 		NOT NULL,
	CreatedDate			DATETIME			NOT NULL,
	UpdatedDate			DATETIME			NOT NULL	
)