This SQL Tutorial focuses on the SQL Server DML statements. DML (Data Manipulation Language) statements are the element in the SQL language that is used for data retrieval and manipulation. Using these statements you can perform operations such as: adding new rows, updating and deleting existing rows, merging tables and so on.
This SQL tutorial allows you to become familiar with the following topics :
- INSERT – adding new rows to a table
- UPDATE – updating values of fields
- DELETE – deleting rows from a table
- MERGE – merging tables
- TRANSACTIONS – learn what transactions are and how they can be managed
Please note – the SQL Server SELECT statement, another DML component, is not covered in this tutorial. For more details about the SQL Server SELECT statement please use the following link.
SQL Server DML – INSERT Statement
The SQL Server INSERT statement is used to add new rows to a table. Before you become familiar with this statement, please take into account the following points:
- Default value – when creating a new table (DDL), it is possible to define a specific column with a default value. This means that unless otherwise indicated, this is the value that you would like to enter into the field of that column. For example: default salary is 5700, and default employment date is ‘2014-23-01’.
- The following INSERT demonstrations are based on the table below, where the default value 5700 is assigned to the emp_sal column and currently the table has a single row
INSERT INTO table_name VALUES (value, value, value …)
Inserting a new row into the table
INSERT INTO emps VALUES (2 , 'David' , 3200 , ’2014/03/20’)
The following table reflects the data in Emps table after the INSERT statement has completed:
- The order of the values must match the order of the columns in the table. In the case above, the first value must be associated with the employee number column, the second value must be associated with the employee name column, and so on.
- The value type must match the type of columns in the table. For example, it is not possible to enter the value 37 into the emp_hiredate column.
- The number of values – must match the number of values in the table. It is not possible to enter more or less values than the number of values in the table (in this case, 4 values).
- To enter a NULL value into one of the fields of one of the columns, the NULL keyword should be used.
INSERT INTO emps VALUES (3 , NULL , 3200 , ’2009/09/09’)
- To enter a DEFAULT value into one of the fields of one of the columns, the DEFAULT keyword should be used.
INSERT INTO emps VALUES (4 , 'Maya', DEFAULT , ’2011/09/09’)
- GETDATE() – you can use the GETDATE () function instead of specifying a date.
INSERT INTO emps VALUES (4 , 'Anne', DEFAULT , GETDATE())
Using a Column List
The following example uses a column list to explicitly specify the values that are inserted into each column. This option provides us with additional flexibility.
INSERT INTO table_name (column_name , column_name , column_name ...) VALUES (value, value, value …)
Inserting a new row into the table.
INSERT INTO emps (emp_id , emp_name , emp_salary , emp_hiredate) VALUES (5 , 'Roy' , 3200 , GETDATE())
- The order of the values must match the order of the columns in the column list. In the case above, the first value must be associated with the employee number column; the second value must be associated with the employee name column, and so on.
- The value type must match the type of columns in the column list. For example, it is not possible to enter the value 37 into the emp_hiredate column.
- The number of values – must match the number of values in the column list. Therefore, there is no need to enter a value into each column of the table.
INSERT INTO emps (emp_id , emp_name) VALUES (6 , 'Roger')
A NULL value is entered into a column that was not specified in the column list, if no default value exists; otherwise, the DEFAULT value is entered.
- To enter a NULL value explicitly into one of the fields of one of the columns, the NULL value should be specified.
INSERT INTO emps (emp_id , emp_name, emp_salary) VALUES (6 , 'Ben' , NULL)
- To enter a DEFAULT value explicitly into one of the fields of one of the columns, the DEFAULT value should be specified.
INSERT INTO emps (emp_id , emp_name, emp_salary) VALUES (7, 'Kim' , DEFAULT)
- GETDATE – you can use the GETDATE() function instead of specifying a date.
SQL Server INSERT INTO SELECT
This method allows copying data items from another table into the requested target table.
INSERT INTO target_table_name (column_name, column_name, column_name ..) SELECT … FROM source_table_name WHERE …
INSERT INTO emps (emp_id , emp_name , emp_salary) SELECT employee_id , last_name , salary FROM employees WHERE department_id = 50
- You can use the INSERT INTO SELECT statement either with or without the column list
- It is not mandatory to include a WHERE statement in the query; however, including such a statement is advisable if you would like to avoid copying the entire table.
SQL Server DML – UPDATE Statement
The SQL Server UPDATE statement is used to modify existing rows.
UPDATE table_name SET column=value, column=value .. WHERE condition
Updating the salary of employee no. 100:
UPDATE employees SET salary = 5000 WHERE employee_id = 100
It is possible to update several fields at the same time, for example update salary, last name and first name of employee no. 100:
UPDATE employees SET salary = 5000 , last_name = 'Doe' , first_name = 'John' WHERE employee_id = 100
A subquery can be nested in the SQL Server UPDATE statement. For example, updating the salary of the employees in department 60 so it would match the average salary of the employees in department 50:
UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 50) WHERE department_id = 60
It is possible to update to a NULL or DEFAULT value.
UPDATE employees SET salary = DEFAULT WHERE last_name = 'King' UPDATE employees SET salary = NULL WHERE department_id = 90
SQL Server DML – DELETE Statement
The SQL Server DELETE statement is used to remove existing rows from a table.
DELETE FROM table_name WHERE condition
Deleting the row that contains the data of employee no. 103:
DELETE FROM employees WHERE employee_id = 103
A subquery can be nested in the DELETE statement.
DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales')
- Executing the SQL Server DELETE statement without using the SQL Server WHERE clause, results in the deletion of all of the rows in the table.
- It is not possible to delete only a single field by using the SQL Server DELETE statement .The SQL Server DELETE statement deletes rows.
- You can write a SQL Server DELETE statement either with or without the SQL Server FROM keyword.
DELETE FROM employees -- or DELETE employees
SQL Server DML – MERGE Statement
The SQL Server MERGE statement is used to synchronise the data of two tables, based on differences found between them, if the same row exists in both tables (row with the same customer id for example), but still each row has different values (each table holds a different phone number of that customer), UPDATE operation will be executed. If the row only exists in one table, INSERT operation will be executed.
The following illustrations demonstrates the merge concept. At the end of each month, the data of Customers table is merged with the data of the Backups table. In the beginning of the month, the only MERGE activity was transferring (INSERT) all rows, as is, to the Backup table (provided that this was the first time that the Backup table was populated).
At the end of the month, due to the changes that were carried out in the Customers table, the data items are merged with the Backup table as follows:
- For customers that already exist in the backup table, an UPDATE operation is carried out, for example, George’s Internet Package will be changed from 2.5 MB to 5.0 MB.
- For new customers, an INSERT operation is carried out, thereby adding a new customer – for example, Roy– to the backup table.
MERGE INTO destination_table alias USING source_table alias ON condition WHEN MATCHED THEN UPDATE SET destination_table_alias.column = source_table_alias.column, destination_table_alias.column = source_table_alias.column … WHEN NOT MATCHED THEN INSERT VALUES (source_table_alias.column, source_table_alias.column .. )
MERGE INTO customers_backup bkup USING customers cust ON (bkup.cust_id = cust.cust_id) WHEN MATCHED THEN UPDATE SET bkup.cust_name = cust.cust_name , bkup.cust_surfing_package = cust.cust_surfing_package WHEN NOT MATCHED THEN INSERT VALUES(cust.cust_id , cust.cust_name , cust.cust_surfing_package)
Transactions are a single unit of various modification commands (such as UPDATE, INSERT, DELETE), which in most cases are associated with a single logical group. The term “a single logical group” refers to a set of operations with logical connection; for example: a batch of DML operations that are meant for updating specific data items in the customers table.
The COMMIT command used to save all changes made by the transaction in the database. The COMMIT command saves all modifications since the last COMMIT or ROLLBACK command.
The ROLLBACK command used to undo changes made by a transaction. The ROLLBACK command can only undo modifications since the last COMMIT or ROLLBACK command that was issued.
SQL Server Autocommit Mode – In SQL Server, by default, every modification (such as UPDATE, DELETE, INSERT) is committed automatically once it completes. In SQL Server you can start an Explicit Transaction (one that you decide when and how to close) using the BEGIN TRAN command.