Different possible ways to create a table and insert a data.
I) Create a table with the help of Create Statement.
Example: Create table titled 'Student'.
Syntax is given below to create a table 'Student'
CREATE TABLE Students( Studentid int IDENTITY(1,1) NOT NULL, Firstname varchar (200) , Lastname varchar (200) , Email varchar (100) )
So this syntax will create a table student, where the Studentid is not null.
Suppose a user wants to insert the data into the table titled 'Student'.
Method 1: Insert statement to insert data is given below.
Insert into Students(Studentid,Firstname,lastname, email) Values(1,'Jaya','Singh', 'Jaya@tutorialride.com')
The result of this query is verified by using following query:
Select * FROM Students
Result:
| Studentid | Firstname | Lastname | Email |
|---|
| 1 | Jaya | Singh | Jaya@tutorialride.com |
Method 2: Insert values into table using another table.
Consider that we have a table titled 'Students' and we have to insert its values into the another table titled 'Studentdemo'
Create table 'Studentdemo'
CREATE TABLE Studentsdemo( Studentid int IDENTITY(1, 1) NOT NULL, Firstname nvarchar (200) , Lastname nvarchar (200) , Email nvarchar (100) )
Now, to insert values of table 'Students' into table 'Studentsdemo' by using following statement.
Insert into Studentsdemo(Studentid,Firstname,lastname, email) SELECT Studentid, Firstname, lastname, email FROM Students
Result of this statement can be verified by using
SELECT * FROM Studentsdemo
Result:
| Studentid | Firstname | Lastname | Email |
|---|
| 1 | Jaya | Singh | Jaya@tutorialride.com |
Note: To insert the records from one table to another the data type of the column should be same.
II) Rename the existing table
ALTER TABLE command is used to rename table.
Alter name of “Studentsdemo” table to Studentscopy.
ALTER TABLE Studentsdemo RENAME TO Studentscopy
III) Consider the table 'Students' given below and ADD column 'Phone' in the existing table.
| Studentid | Firstname | Lastname | Email |
|---|
| 1 | Jaya | Singh | Jaya@tutorialride.com |
| 2 | Shruti | Shrabya | Shruti@tutorialride.com |
ALTER TABLE command is used to add column to an existing table.
The statement is given below.
ALTER TABLE Students ADD Phone INT Null
Result:
| Studentid | Firstname | Lastname | Email | Phone |
|---|
| 1 | Jaya | Singh | Jaya@tutorialride.com | null |
| 2 | Shruti | Shrabya | Shruti@tutorialride.com | null |
Note: By using ALTER TABLE statement to add new column in the table. The constraint null is applicable by default if the table is not empty.
To use NOT NULL constraint the table must be empty.
