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.
- Connect to Microsoft SQL Server Instance
- Expand the Object Explorer and select the Database oracleappshelpDB
- Right click on Table -> New -> Table
- Enter the Table Column Name , data type value
- Select if column is to be allowed as NULL or NOT NULL
- 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
SQL – Alter 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
)