This article shows how to use DML (Data Manipulation Language) statements.
The T-SQL DML statements are used to manipulate(add, modify, delete) records from database tables.
DML statements consist of the following T-SQL statements: INSERT, UPDATE, DELETE, MERGE, BULK INSERT.
INSERT
The T-SQL INSERT command is used to add new records to the table.
CREATE TABLE books(id int, name varchar(100), price int);
INSERT INTO books(id, name, price)
VALUES (11, 'T-SQL book', 100);
INSERT INTO books(id, name, price)
VALUES (12, 'Book_12', 120);
INSERT INTO books(id, name, price)
VALUES (13, 'Book_13', 90);
UPDATE
The T-SQL UPDATE statement is used to modify existing records in the table.
UPDATE books SET name='SQL Server' WHERE id=12;
UPDATE books SET price=price+10 WHERE price >=100;
DELETE
The T-SQL DELETE statement is delete existing records in the table.
DELETE FROM books WHERE id=13;
DELETE FROM books WHERE price > 100;
MERGE
The T-SQL MERGE statement is used to insert, update, or delete rows on a target table.
-- Create target table: test1
-- Create source table: test2
CREATE TABLE test1(id int, name varchar(50), price int);
CREATE TABLE test2(id int, name varchar(50), price int);
-- Insert records
INSERT INTO test1(id, name, price)
VALUES (1,'a',10), (2,'b',20), (3,'c',30);
INSERT INTO test2(id, name, price)
VALUES (3,'d',25), (4,'e',40), (5,'f',50);
-- Select all records
SELECT * FROM test1
UNION ALL
SELECT * FROM test2;
-- Start merge records from test1 to test2
MERGE test2 AS TARGET
USING test1 AS SOURCE
ON SOURCE.id = TARGET.id
WHEN MATCHED THEN
UPDATE SET TARGET.price = SOURCE.price, TARGET.name = SOURCE.name
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, name, price)
VALUES (SOURCE.id, SOURCE.name, SOURCE.price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
SELECT @@ROWCOUNT;
GO
