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.

Microsoft SQL Server Training Online Learning Classes Insert Delete Update Truncate
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.

  2. INSERT INTO Table1 VALUES (1, 'aaa')
  3. Inserting partial record into table - inserting selected column values

  4. INSERT INTO Table1 (Id) VALUES(2)
    // OR //
    INSERT INTO Table1(Id) SELECT 2
  5. Inserting multiple records simultaneously.

    INSERT INTO Table1
                       SELECT 5,'bbb' UNION ALL
                       SELECT 6,'ccc' UNION ALL
                       SELECT 7,'ddd'
  6. Inserting a record with default values only.

  7. -- Inserts defaults if available otherwise NULL' 
    INSERT INTO table1 DEFAULT VALUES 
  8. Inserting records from existing table.

  9. INSERT INTO table2(Id2,Name2)
       SELECT Id, name FROM table1
  10. Inserting records from a Stored Procedure.

  11. 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.
  12. 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

  2. UPDATE table1
    SET id=20
    WHERE id=2;
  3. Updating multiple columns

  4. UPDATE Table1
    SET id=20, name='KKK'
    WHERE id=2;
  5. Updating a column with sub-query. (Q: Set name=kkk for maximum id candidate)

  6. UPDATE Table1
    SET name='KKK'
    WHERE id IN (Select Max(id) from Table1);
  7. Updating a column with two sub-queries.

  8. UPDATE Table1
    SET name = (Sub-query1)
    WHERE id IN (Sub-query2);
  9. Updating a column with CASE statement.(Q: Interchange male & female for SEX column)

  10. UPDATE Table1
    SET Sex=CASE WHEN(sex='male')   THEN 'female'
                 WHEN(sex='female') THEN 'male'
                 ELSE 'UNKNOWN' END;
  11. Setting a column to its default value.

  12. UPDATE Table1
    SET name=DEFAULT
    WHERE id =101;
  13. UPDATE with JOINS

  14. --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.

  2. DELETE Table1 // OR // DELETE FROM Table1

  3. Deleting a particular records

  4. DELETE FROM Table1 WHERE id=101
  5. Deleting with JOINS

  6. -- 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.

1 comment: