DML Statements For Data Manipulation

Discussion in 'SQL Server' started by MinalS, Apr 18, 2016.

  1. In SQL, there are requirements when user needs to perform data manipulations like inserting, deleting, updating the data in the database tables. The various DML statements are used for performing the operations.

    Adding data in table



    You can add a row to a table using the INSERT statement. The INSERT statement has the following syntax:

    Code:
    INSERT [ INTO ] tablename columns values ( DEFAULT | values | select_statement )
    
    Where,
    • tablename: The name of the table where the data is added
    • columns: It is used when user needs to add partial data in a table
    • DEFAULT: The clause used for adding the default value for a particular column
    • values: The values for table columns to be added as a row in the table
    • select_statement: The SELECT statement used for adding rows in a table
    User can use any of the following statements for adding rows in a table:

    Code:
    1) INSERT Management VALUES ( ‘Peter’, 222, ‘Germany’ )
    2) INSERT Management ( Name, ID, Location) VALUES ( ‘Sam’, 333, ‘London’ )
    3) INSERT Management ( Name, Location, ID) VALUES ( ‘Sam’,‘London’,333 )
    
    Multiple rows can be added to a table using the T-SQL row constructor. The following statement is used for adding the rows in the table using the constructor.

    Code:
    INSERT Management (Name, ID, Location ) VALUES 
    ( ‘Peter’, 222, ‘Germany’ ),
    ( ‘Sam’, 333, ‘London’)
    
    Adding partial data in table

    User can add partial data in a table using the SQL query. It is not mandatory to add all the values to the table. It is necessary for the user to define the column name for which the values are added using the INSERT statement.

    Consider an example of management table where values for name and ID column are added.

    Code:
    INSERT Management ( Name, ID ) Values ( ‘Peter’, 222, ‘Germany’)
    
    Copying data from the existing table

    The SELECT statement is used for copying data from an existing table. The table studentinfo contains marks for different students. User needs to copy the marks of student > 50 from the studentinfo table into academicsinfo table.

    Code:
    SELECT * INTO academicsinfo from studentinfo where marks > 50 
    
    A new table as academicsinfo is created. The table structure will be similar to studentinfo.

    Adding data to an identity column

    An identity column is the one in which the values are automatically added through the database. There is no need to define the values for the column.

    In Management table, the ID column is an identity column. You can insert the value in the table using the following statement.

    Code:
    INSERT INTO Management ( Name, Location ) Values ( ‘Steve’, ‘New york’)
    
    If user wants to add the value to the identity column, use the SET_IDENTITY_INSERT statement. The property values needs to be set as ON in order to add values to it. The value for the property is OFF.

    Code:
    SET_INSERT_IDENTITY Management ON
    
    Now, user can add the value for ID column. The following statement is used for adding value to the table.

    Code:
    INSERT INTO Management Values (‘Jerry’, 444, ‘Cambridge’)
    
    Adding spatial data to the table

    SQL provides user to easily access the geographical data for a particular location using the latitude and longitude. The GEOGRAPHY data type is used for accessing data for the locations. The X and Y co-ordinates are used. The longitude defined the X – coordinate and latitude is defined using Y – coordinate.

    The point value is taken as an input parameter using the Geography::Parse method. A space is used for separating the co – ordinates.

    Code:
    INSERT INTO countrylocation ( countryID, countrylocation ) VALUES ( 201, Geography::Parse(‘POINT ( -21.036, 33.6565)’) )
    

    Deleting data



    It is very easy for the user to remove the data from an existing table in the database. The DELETE statement is used for deleting the data.

    Syntax:

    Code:
    DELETE [ from ] tablename [ from ] table(s) [ WHERE condition ]
    
    Where,
    • tablename: the name of the table
    • table(s): The condition for deleting the data is assigned
    • condition: The condition used for defining the rows to be deleted
    Example:
    Code:
    DELETE Management where ID = 222
    
    Deleting all the data from table

    The DELETE statement is used for removing all the data from the database table. The syntax for deleting data is:

    Code:
        DELETE TABLE tablename
    
    The TRUNCATE statement is used for deleting the data from the table. The execution of the statement is more quick than DELETE.

    Code:
        TRUNCATE TABLE tablename
    
    Example:
    Code:
        DELETE TABLE Management
    

    Updating data



    Once the table is created, user adds the data to the tables. Sometimes, there is a need to modify the data from the tables. For data modification, the UPDATE statement is used. More than one column of the row can be updated by the user.

    Syntax:

    Code:
    UPDATE tablename 
    SET columnname = value [ , columnname = value ]
    [ from tablename ] 
    [ where condition ]
    
    Where,
    • tablename is the name of the table
    • columnname is the column name used for modifying the table
    • value is used for updating the column of the table
    • from tablename defines the table used for the updating the data
    • condition defines the rows to be updated
    Example:
    Code:
    update productdata set location = ‘London’ where ProductID = 308
    
    Output:

    [​IMG]

    Retrieving the updated data



    The data manipulation operations like insert, delete and update are performed on the database tables. The number of rows which are modified by the operations are displayed. User needs the data which is changed using the DML statements.

    Consider the productdata table. We have a requirement to update the quantity in the productdata table. But, you need to maintain the data of the product. The old data needs to be archived and later update in the productdata table.

    The OUTPUT clause in SQL is used for accessing the rows which are affected by the DML statements. The clause can be used with the INSERTED and DELETED statement. A virtual table as INSERTED is created when you add a row to the table. A virtual table as DELETED is created when you remove a row from the table.

    Create a virtual table as productdata1 with the following columns in it.

    Code:
    Create table productdata1(
    ProductID int not null,
    productname varchar(20) not null,
    quantity int not null,
    location varchar(20) not null,
    price int not null)
    
    Execute the following query to update the productdata table and add the old value to the productdata1 table.

    Code:
    update productdata 
    set quantity = 1500
    OUTPUT DELETED.* INTO productdata1
    where ProductID = 417
    
    The value added to the productdata1 table is as shown below:

    [​IMG]

    The OUTPUT clause is used when user needs to send the modified data to the client. Consider the following query to update the location as New York.

    Code:
    update productdata 
    set location = ‘New York’
    OUTPUT INSERTED.*
    where ProductID = 417
    
    [​IMG]

    Data comparison



    It is necessary for the user to keep the backup of the data from various tables in the database. If the data in a table is modified, the changes must be added to the backup table. Both the table must contain same values. If there are changed in the table, user needs to perform similar operation on the backup table.

    To overcome this task, the MERGE statement is used for simultaneously updating both the tables. All the data modifications are performed on the table using a single statement.

    Syntax:

    Code:
    MERGE 
        [TOP ( expression ) [ PERCENT]
        [ INTO ] <targetable> [ WITH ( <mergehint> ) ] [ [ AS ] ] table_alias ]
        USING <tablesource>
        ON <mergecondition>
        [ WHEN MATCHED [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [ …n ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
            THEN <merge_not_matched> ]
        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [ …n ]
        [ <output_clause> ]
    
    Where,
    • TOP ( expression ) [ PERCENT ] : The number or percentage of rows changed
    • <targetable> : The table or views where the rows are added by the WHEN clause
    • <tablesource> : The table or views where the rows matching the target table are accessed
    • <mergecondition> : The condition used to check if the rows from the source and destination table are same
    • WHEN MATCHED THEN <merge_matched>: the action to be done on the target table rows when there is a match between the source and target tables
    • WHEN NOT MATCHED BY SOURCE THEN <merge_matched>: An update or delete operation is performed on the rows present in the target table
    • <output_clause>: A row is provided for update, insert and delete operations
    Example:

    Consider a demo table containing two column as demoid and location. User needs to take the backup from the demo table in the demobackup table.

    Code:
    SELECT * INTO demobackup from demo
    
    Now, change the demo table by using the manipulation statements.

    Code:
    insert into demo values ( 4, ‘Paris’)
    delete demo where demoid = 3
    
    The demo table contains following values:

    [​IMG]

    User needs to add the changed of the demo table to the backup table. The MERGE statement is used for reflecting the changes.

    Code:
    merge demobackup as target
    using demo as source
    on ( target.demoid = source.demoid )
    when matched and target.location <> source.location
    then update set target.location = source.location
    when not matched then
    insert values ( source.demoid, source.location)
    when not matched by source then
    delete;
    
    Now, you can view the values added in the backup table by executing the following statement.

    Code:
    select * from demobackup
    
    [​IMG]
     
    Last edited by a moderator: Jan 21, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice