DML in SQL Server: INSERT, UPDATE, DELETE

DML commands change the DATA inside tables.

INSERT -> add rows
UPDATE -> change rows
DELETE -> remove rows

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS Products;
GO

CREATE TABLE Products
(
    ProductID   INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Price       INT,
    Stock       INT
);
GO

INSERT one row

INSERT INTO Products (ProductID, ProductName, Price, Stock)
VALUES (1, 'Pen', 10, 100);

SELECT * FROM Products;

(1 row affected)

INSERT many rows at once

INSERT INTO Products (ProductID, ProductName, Price, Stock)
VALUES (2, 'Pencil',   5,  200),
       (3, 'Notebook', 50, 80),
       (4, 'Eraser',   3,  150),
       (5, 'Bag',      500, 10);

SELECT * FROM Products;

(4 rows affected)

INSERT only some columns (others become NULL)

INSERT INTO Products (ProductID, ProductName)
VALUES (6, 'Ruler');

SELECT * FROM Products;     -- Ruler has NULL price and stock

(1 row affected)

UPDATE one row

UPDATE Products
SET Price = 8, Stock = 50
WHERE ProductID = 6;

SELECT * FROM Products;

(1 row affected)

UPDATE many rows (increase price of cheap items by 2)

UPDATE Products
SET Price = Price + 2
WHERE Price < 10;

SELECT * FROM Products;

(3 rows affected)

DELETE one row

DELETE FROM Products
WHERE ProductID = 4;

SELECT * FROM Products;     -- Eraser is gone

(1 row affected)

DELETE many rows

DELETE FROM Products
WHERE Stock < 20;

SELECT * FROM Products;     -- Bag is gone

-- !!! WARNING !!!
-- UPDATE or DELETE without WHERE changes EVERY row:
--   DELETE FROM Products;            -- deletes all rows
--   UPDATE Products SET Price = 0;   -- every price becomes 0
-- Always write the WHERE first, and check it with a SELECT.

(1 row affected)

Copy rows into a new table (SELECT INTO)

DROP TABLE IF EXISTS ProductsBackup;
GO

SELECT * INTO ProductsBackup FROM Products;

SELECT * FROM ProductsBackup;

-- =============================================
-- DELETE vs TRUNCATE vs DROP
--   DELETE   -> removes rows (all or with WHERE), table stays    (DML)
--   TRUNCATE -> removes ALL rows, no WHERE, faster, table stays  (DDL)
--   DROP     -> removes the whole table                          (DDL)
-- =============================================

(4 rows affected)