MS SQL Server Insert Data
- Insert statement is used to add data or records to the table.
- Insertion operation in two ways within a single query: Add data in a single row and Add data in multiple rows
- Syntax to add data in a single row:
INSERT INTO[database_name].[dbo].[table_name](column_name1, column_name2,...)VALUES(value1,value2,..);
- database_name is the name of the database where the table is stored and where we are going to insert data.
- Schema_name indicates the schema to which the table belongs.
- table_name is the name of the table in which we are going to insert data
- column_name is the name of the columns of our table.
- value represents the value corresponding to the column name.
Syntax to add multiple records:
INSERT INTO[database_name].[dbp].[table_name]VALUES(value1,value2,...valueN)(value1,value2,...valueN)(value1,value2,...valueN);
Example:
Insert data for the table University:
CREATE TABLE University.dbo.student(Id INT IDENTITY PRIMARY KEY, Name VARCHAR(65)NOT NULL, Branch CHAR(10), CGPA FLOAT);
- A single record for all the fields
INSERT INTO student(Id,Name,Branch,CGPA)VALUES('EN19CS301254','Ravi','CSE',9.34);
- Table created after execution of the following Query, verify the data using SELECT statement:
SELECT * FROM student;
| Id |
Name |
Branch |
CGPA |
| EN19CS301254 |
Ravi |
CSE |
9.34 |
- A single record to be inserted into all columns, it is optional to specify the column name
INSERT INTO student VALUES('EN19CS301258','Savi','CSE',9.38);
- Table created after execution of the following Query, verify the data using SELECT statement:
SELECT * FROM student;
| Id |
Name |
Branch |
CGPA |
| EN19CS301258 |
Savi |
CSE |
9.38 |
- Multiple records for all the fields
INSERT INTO student VALUES('EN19CS301254','Ravi','CSE',9.34)('EN19CS301246','Savi','CSE',8.23)('EN19CS301248','Aayush','CSE',7.25);
- Table created after execution of the following Query, verify the data using SELECT statement:
SELECT * FROM student;
| Id |
Name |
Branch |
CGPA |
| EN19CS301254 |
Ravi |
CSE |
9.34 |
| EN19CS301246 |
Savi |
CSE |
8.23 |
| EN19CS301248 |
Aayush |
CSE |
7.25 |
- Records for not all fields
INSERT INTO student(Id,Name)VALUES('EN19CS301258','Kavi');
- Table created after execution of the following Query, verify the data using SELECT statement:
SELECT * FROM student;
| Id |
Name |
Branch |
CGPA |
| EN19CS301254 |
Ravi |
CSE |
9.34 |
| EN19CS301246 |
Savi |
CSE |
8.23 |
| EN19CS301248 |
Aayush |
CSE |
7.25 |
| EN19CS301258 |
Kavi |
NULL |
NULL |
