Microsoft SQL Server DML Commands: A Comprehensive Guide

In Microsoft SQL Server, Data Manipulation Language (DML) commands are used to interact with the data stored in the database. DML commands enable you to retrieve, insert, update, and delete data from tables. In this article, we will explore the commonly used DML commands in SQL Server and their syntax with examples.

SELECT

The SELECT statement is used to retrieve data from one or more tables in the database. It allows you to specify the columns to retrieve, apply filtering conditions using the WHERE clause, and even join multiple tables to combine data.

    SELECT column1, column2
    FROM table_name
    WHERE condition;
  

INSERT

The INSERT statement is used to add new records to a table. You can insert explicit values or use a subquery to insert data from another table.

    INSERT INTO table_name (column1, column2)
    VALUES (value1, value2);
  

UPDATE

The UPDATE statement is used to modify existing records in a table. You can use the SET clause to specify the new values and the WHERE clause to filter which records to update.

    UPDATE table_name
    SET column1 = new_value1, column2 = new_value2
    WHERE condition;
  

DELETE

The DELETE statement is used to remove records from a table. Like the UPDATE statement, you can use the WHERE clause to specify which records to delete.

    DELETE FROM table_name
    WHERE condition;
  

MERGE

The MERGE statement combines the INSERT, UPDATE, and DELETE operations into a single statement. It allows you to perform insertions, updates, and deletions based on a specified condition, all within a single transaction.

    MERGE INTO target_table AS T
    USING source_table AS S
    ON T.id = S.id
    WHEN MATCHED THEN
      UPDATE SET T.column1 = S.column1
    WHEN NOT MATCHED THEN
      INSERT (column1, column2)
      VALUES (S.column1, S.column2);
  

Conclusion

DML commands are essential for manipulating data in Microsoft SQL Server. By mastering the SELECT, INSERT, UPDATE, DELETE, and MERGE statements, you can effectively retrieve, insert, update, and delete data from your SQL Server database tables. Understanding the syntax and usage of these commands will enable you to perform powerful data operations and manage your data effectively.