How To Create Tables And Insert Data In SQL Server 2017


Tables

In a database data's are stored in tables. Tables can be identified by their unique names. A table will have specified number of columns but it can have "N" numbers of rows. Each columns in a table must contain unique name and data type for the column. Rows contain the data for each column in a table.

Create Table

The Create keyword is used to create a table in SQL server.

Create Table Syntax:

Create Table "Table-Name" #break ( #break   "ColumnName-1" "DataType", #break   "ColumnName-2" "DataType", #break   ...."N" "DataType" #break );

Example: To create a table "Students" with 3 columns StudentName, Country and Age

Create Table Students #break ( #break   StudentName Varchar(250), #break   Country Varchar(50), #break   City Varchar(50) #break );

Alter Table

Alter keyword is used to alter/modify the column name, column datatype, to add a new column, to delete/drop an existing column and to rename the table name in SQL server.

Add Column Syntax:

Alter Table "Table-Name" Add "Column-Name" "DataType";

Example To Add Column Age In Students Table

Alter Table Students Add Age Char(1);

Alter/Modify Column Data Type Syntax:

Alter Table "Table-Name" Alter "Column-Name" "DataType";

Example To Change The DataType For Column Age

Alter Table Students Alter Column Age int;

To Delete/Drop Column From A Table Syntax:

Alter Table "Table-Name" Drop Column "Column-Name";

Example To Delete Column Age From Table Students:

Alter Table Students Drop Column Age;

To Rename Column Name In A Table Syntax:

Exec sp_rename '"Table-Name"."Column-Name"', "New_Column-Name", 'Column';

Example To Rename Country Column To CountryName:

Exec sp_rename 'Customers.Country', CountryName, 'Column';

To Rename A Table Name Syntax:

Exec sp_rename "Table-Name", "New_Table-Name";

Example To Rename Table Students To StudentDetails

Exec sp_rename Students, StudentDetails;

Drop Table

The Drop keyword is used to delete/drop a table in SQL Server.

Drop Table Syntax:

Drop Table "Table-Name";

Example To Delete Table StudentDetails:

Drop Table StudentDetails;

Using Microsoft SQL Server Management Studio 2017 GUI

Using Microsoft SQL Server Management Studio 2017 GUI you can create table manually.

Insert Command

The keyword INSERT INTO is used to insert new records in a table.

By Specifying Both Column Names and Values to Be Inserted

To Insert Record Syntax:

Insert Into "Table-Name" ("ColumnName-1","ColumnName-2","ColumnName-3",..."ColumnName-N") #break Values ("Value-1","Value-2", "Value-3",..."Value-N")

Example To Insert A Record In StudentDetails Table:

Insert Into StudentDetails(StudentName,Country,City,Age) #break Values('Student 1','India','Bangalore',35);

Note: All text values must enclose in single quotes.Numeric fields without single quotes.

By Specifying Only the Values

You can also insert a record by just specifying the values only, even without specifying the column names. You should make sure that the order of the values and columns are in the same order in the SQL query.

Insert Record Without Column Name Syntax:

Insert Into "Table-Name" Values ("Value-1","Value-2", "Value-3",..."Value-N")

Example To Insert Values Without Column Name In StudentDetails Table:

Insert Into StudentDetails Values('Student 1','India','Bangalore',35);

Insert Record From One Table To Another Table

You can also insert record into a table from another table using the SELECT statement.

Syntax:

Insert Into "First_Table-Name" ("ColumnName-1","ColumnName-2","ColumnName-3",..."ColumnName-N") #break Select "ColumnName-1","ColumnName-2","ColumnName-3",..."ColumnName-N" From "Second_Table-Name";

Example To Insert Record From StudentDetails Table To StudentInfo Table:

Insert Into StudentInfo(StudentName,Country,City,Age) #break Select StudentName,Country,City,Age From StudentDetails;
Share On