In this tutorial, we will learn INSERT, UPDATE, DELETE and TRUNCATE data manipulation operation.

INSERT Statement

We used INSERT INTO statement to insert record(s) in a table. To see the different behavior of this statement we will use the following table in our upcoming example
       
 CREATE TABLE TblStudent
 (
	SRNo VARCHAR(15),
	StudentName VARCHAR(50),
	FatherName VARCHAR(50),
	CourseCode VARCHAR(15),
        State VARCHAR(50)
 )
 
 GO
 
 CREATE TABLE TblMarks
 (
         SRNo VARCHAR(15),
         TotalMarks INT
 )
1. Insert a single row into table :- The following insert statement inserts a single row into the TblStudent table. Because we have supplied values for all columns in the order as they are listed in the table so it's not compulsory to specify the column list in the INSERT INTO statement. But I always recommend you to specify the column list to avoid errors after adding a new column later into the table.
         
INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode,State) 
VALUES ('SR01','Kamal','Harish','BCA001','UP')
2. Insert multiple rows into a table without select statement
INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode) 
VALUES ('SR01','Kamal','Harish','BCA001'),('SR02','Sachin','Gagan','BCA002')
,('SR03','Kapil','Raj','BCA003')
3. Insert multiple rows into a table with select statement
INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode) 
SELECT 'SR01','Kamal','Harish','BCA001' UNION ALL
SELECT 'SR02','Sachin','Gagan','BCA002' UNION ALL
SELECT 'SR03','Kapil','Raj','BCA003'
4. Insert a sigle row with default values into table
INSERT INTO TblStudent DEFAULT VALUES
5. Insert rows from existing table
INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode) 
SELECT SRNo, StudentName, FatherName, CourseCode 
FROM TblStudentBackup
6. Insert rows from stored procedure
-- first create a stored procedure
CREATE PROCEDURE myProc1
AS
    SELECT 'SR01','Kamal','Harish','BCA001','UP' UNION ALL
    SELECT 'SR02','Sachin','Gagan','BCA002','UP' UNION ALL
    SELECT 'SR03','Kapil','Raj','BCA003','UP'

-- Inserting values into table using stored procedure
INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode,State) 
EXEC myProc1

UPDATE Statement

Syntax 
UPDATE table_name
SET column_name1 = expression1 [,column_name2 = expression_2]...
[FROM table_source [[AS] table_alias]
[WHERE search_condition]
1. A simple UPDATE statement 
UPDATE TblStudent 
SET StudentName='Vijit'
WHERE SRNo='SR01'
2. UPDATE multiple columns 
UPDATE TblStudent 
SET StudentName='Sumit',FatherName='Harsh'
WHERE SRNo='SR02'
3. UPDATE a column with a sub-query. (Question: Set StudentName=Amar Kumar for maximum id student) 
UPDATE TblStudent
SET name='Amar Kumar'
WHERE id IN (Select Max(id) from TblStudent);
4. Updating a column with multiple sub-queries. 
UPDATE TblStudent
SET StudentName = (Sub-query1)
WHERE SRNo IN (Sub-query2);
5. Updating a column using CASE statement.(Question: Interchange male & female for SEX column) 
UPDATE TblStudent
SET Sex=CASE WHEN(sex='male')   THEN 'female'
             WHEN(sex='female') THEN 'male'
             ELSE 'UNKNOWN' END;
6. UPDATE using JOINS:- Update State='HR' for the students who got TotalMarks more than 450 
UPDATE TblStudent SET State='HR' FROM TblStudent S JOIN TblMarks M
ON S.SRNo=M.SRNo 
AND M.TotalMarks>450

DELETE

1. DELETE all rows from a table 
DELETE FROM TblStudent
2. DELETE a specific row from a table 
DELETE FROM TblStudent WHERE SRNo='SR01'
3. DELETE using JOINS:- delete the students who got TotalMarks more than 450 
DELETE S FROM TblStudent S JOIN TblMarks M
ON S.SRNo=M.SRNo 
AND M.TotalMarks>450

TRUNCATE

TRUNCATE removes all rows from a table, without logging the individual row deletions. It is similar to the DELETE statement with no WHERE clause; however, It is faster than delete because it uses fewer system and transaction log resources.

Syntax :- TRUNCATE TABLE TableName 

Example:-- TRUNCATE TABLE TblStudent
 

Difference between DELETE and TRUNCATE in SQL Server

1. Using DELETE statement we can delete filtered(one or many) rows by using WHERE clause whereas TRUNCATE does not have any concept of WHERE clause. 
2. DELETE is a DML(Data Manipulation Language) command. While TRUNCATE is a DDL(Data Definition Language) command. 
3. DELETE command locked a row before removing it. In TRUNCATE command, data page is locked before removing the table data. 
4. DELETE command is slower than TRUNCATE command. 
5. We can use DELETE command with views whereas TRUNCATE command can not be used with views. 
6. DELETE statement does not reset the identity column. TRUNCATE command reset the identity column. 
7. The DELETE command removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE command removes the data by deallocating the data pages.