SQL #5: DML

1 minute read

This is the fifth in a series of SQL notes I made during the Kubrick Data Engineering training course. The others are:
#1: Database Design
#2: SQL Design
#3: DQL
#4: DDL
#6: Sorting
#7: Joining
#8: Temporary Tables
#9: Programmable Objects
#10: Indexing
#11: Operation & Optimization

All the posts in this series are my personal note taking and may be updated as the course progresses.

DML consists of INSERT, UPDATE, and DELETE.

INSERT

INSERT is used to add rows of data to a table. Individual values can be added with VALUES

INSERT INTO Person ([Name])
VALUES (‘Joe Bloggs’)

A full range can be added using SELECT

INSERT INTO Person ([Name], DOB, Postcode)
SELECT *
FROM Persontable2

INSERT creates a temporary INSERTED table during transaction which is a mirror of the original. Rules can be applied to the temp table using triggers.

UPDATE

UPDATE - Can specify values to be entered into columns of an existing table. Creates both INSERTED and DELETED temporary tables representing the before and after states of the updated table.

DELETE

There are three versions of DELETE, they are:

  • DROP: Completely delete table
  • TRUNCATE: Total wipe of table, resets ID’s, much faster than delete.
  • DELETE: Deletes all rows in table but ID’s remain. Also creates a temporary DELETED table that can be triggered on. DELETED table means DELETE is slower than TRUNCATE.

MERGE

Merge can be used to update tables in a safe and repeatable way. It will perform an UPDATE on existing rows and an INSERT on nonexisting rows. If the rows are already ‘matched’ then it will just leave it.

MERGE production.product AS TARGET
USING stage.product AS SOURCE
ON TARGET.sku = SOURCE.sku
WHEN MATCHED THEN
	UPDATE SET price=SOURCE.price
WHEN NOT MATCHED THEN
	INSERT (sku, name, price)
	VALUES (SOURCE.sku, SOURCE.name, SOURCE.price);

Updated: