SqlTutorial-1 : Lesson-3 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Basic Data Manipulating Operation: INSERT-DELETE-UPDATE-TRUNCATE

 For performing CRUD(Create-Read-Update-Delete) operations we need DML statements; In this article, we will discuss about various behaviors of these commands with bunch of examples. Each example demonstrates a different behavior of each command.

INDEX

• INSERT        - To insert record into the table
• UPDATE       - To modify values of table except primary key
• DELETE       - To delete existing record from a table
• TRUNCATE - To empty the table with clearance to its log and identity value
                             without raising Trigger
  

Note: Truncate is not DML statement it is DDL statement;
             It is same as DROP-TABLE except it preserves table structure

1: INSERT: Inserting values in the table.

Examples:

CREATE TABLE Table1 (Id INT, name VARCHAR(15));

The following are various types of record insertion into the above created table "Table1"

1. Inserting a record into table.

INSERT INTO Table1 VALUES (1, 'aaa')

2. Inserting partial record into table - inserting selected column values

INSERT INTO Table1 (Id) VALUES(2)
// OR //
INSERT INTO Table1(Id) SELECT 2

3. Inserting multiple records simultaneously.

   INSERT INTO Table1
                      SELECT 5,'bbb' UNION ALL
                      SELECT 6,'ccc' UNION ALL
                      SELECT 7,'ddd'

4. Inserting a record with default values only.

-- Inserts defaults if available otherwise NULL' 
INSERT INTO table1 DEFAULT VALUES 

5. Inserting records from existing table.

INSERT INTO table2(Id2,Name2)
   SELECT Id, name FROM table1

6. Inserting records from a Stored Procedure.

Syntax:

INSERT [ INTO] table_or_view_name[ ( column_list ) ] EXEC stored_procedure_name



Example:

-- Create Stored procedure
CREATE PROCEDURE myProc1
AS
          SELECT 1, 'mmm' UNION ALL
          SELECT 2, 'nnn'

--Insert the values into table by executing SP
INSERT INTO table1
     EXEC myProc1     -- 2 rows inserted.

7. BULK INSERT: inserting records from .txt file (Note change Id column type int=>char)

   -- myrecords.txt file. 101 aabb 102 bbcc
   BULK INSERT Table1
   FROM 'c:\myrecords.txt'
   WITH (  DATAFILETYPE = 'char',
                  FIELDTERMINATOR = '\t',
                  ROWTERMINATOR = '\n')
   
   
   For BULK INSERT destination table columns must be character type (non-integer and non-real). If table columns are integer type and real type then, you need to add attribute WITH(FORMATFILE=’xml file path’). The format file is an xml file containing structure for mapping columns of source file to destination table.

2: UPDATE: Updating values in the table.

Syntax:

  UPDATE table_name
  SET column_name_1 = expression_1 [, column_name_2 = expression_2]...
  [FROM table_source [[AS] table_alias]
  [WHERE search_condition]

Examples:

1. Simple update to a column

UPDATE table1
SET id=20
WHERE id=2;

2. Updating multiple columns

UPDATE Table1
SET id=20, name='KKK'
WHERE id=2;

3. Updating a column with sub-query. (Q: Set name=kkk for maximum id candidate)

UPDATE Table1
SET name='KKK'
WHERE id IN (Select Max(id) from Table1);

4. Updating a column with two sub-queries.

UPDATE Table1
SET name = (Sub-query1)
WHERE id IN (Sub-query2);

5. Updating a column with CASE statement.(Q: Interchange male & female for SEX column)

UPDATE Table1
SET Sex=CASE WHEN(sex='male')   THEN 'female'
             WHEN(sex='female') THEN 'male'
             ELSE 'UNKNOWN' END;

6. Setting a column to its default value.

UPDATE Table1
SET name=DEFAULT
WHERE id =101;

7. UPDATE with JOINS

--Consider the following tables StdInfo(rno,name,address), StdRes(rno, m1,m2,m3,total) // Question: CHANGE address to HYD for student whose total marks is 450.

Syntax:

UPDATE SET col1 = [,col2 = ] FROM JOIN ON ] WHERE ]

Example:

UPDATE StdInfo
SET address='Hyd'
FROM StdInfo t1
INNER JOIN StdRes t2 ON t1.rno=t2.rno
WHERE total=450;

3: DELETE: Deleting all or selected rows.

Syntax:

DELETE [FROM { TableName | [ JOIN ON ] } [WHERE ]

Examples:

1. Deleting all records from the table.

DELETE Table1 // OR // DELETE FROM Table1

2. Deleting a particular records

DELETE FROM Table1 WHERE id=101

3. Deleting with JOINS

-- Consider the following tables StdInfo(rno,name,address), StdRes(rno, m1,m2,m3,total) -- Question1: DELETE a record from StdInfo whose total=450
DELETE stdInfo
FROM stdInfo t1 INNER JOIN stdrest t2 ON t1.rno=t2.rno
WHERE total=450

Question2: DELETE matched records from both tables

DELETE stdInfo, stdRes
FROM stdInfo t1 INNER JOIN stdrest t2 ON t1.rno=t2.rno

You can drop all tables in a database using sp_MSforeachtable stored procedure. a) EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" – Deletes all tables of current dbase. b) EXEC sp_MSforeachtable @command1 = "drop table aa" – Deletes single table ‘aa’

4: TRUNCATE command to emptying whole table.

• Removes all rows from a table without logging the individual row deletions.
• TRUNCATE TABLE is functionally the same as the DELETE without WHERE clause;

Syntax:

TRUNCATE TABLE [ {database_name.schema_name..} ] Table_name

Example

TRUNCATE TABLE employee;

TRUNCATE VS DELETE Statement:
Differences between TRUNCATE and DELETE

• TRUNCATE empties the entire table whereas DELETE removes the rows from the table.
So, deleting only particular records is not possible using TRUNCATE but using DELETE with WHERE option it is possible.
• TRUNCATE is the DDL operation that cannot be rolled-back whereas DELETE is the DML operation that can be rolled back.
• TRUNCATE is much faster since it simply de-allocates the table’s memory without logging operations whereas DELETE removes records row-by-row and logs each operation so performance decreases.
• TRUNCATE resets the @@Identity of a table whereas DELETE does not.
• TRUNCATE does not activates the DML Triggers whereas DELETE activates.