SQL - INSERT

Overview

Insert rows explicitly or from another query.

INSERT INTO departments (name) VALUES ('Engineering'), ('HR');
INSERT INTO employees (first_name, department_id, salary, hired_at)
VALUES ('Ada', 1, 120000.00, NOW());

-- from SELECT
INSERT INTO employees (first_name)
SELECT name FROM departments;

INSERT INTO departments (name) VALUES (N'Engineering'), (N'HR');
INSERT INTO employees (first_name, department_id, salary, hired_at)
VALUES (N'Ada', 1, 120000.00, SYSDATETIME());

-- from SELECT
INSERT INTO employees (first_name)
SELECT name FROM departments;

INSERT INTO departments (name) VALUES ('Engineering'), ('HR');
INSERT INTO employees (first_name, department_id, salary, hired_at)
VALUES ('Ada', 1, 120000.00, datetime('now'));

-- from SELECT
INSERT INTO employees (first_name)
SELECT name FROM departments;

Prerequisite

Load the Standard Test Data.

Expected Output (live; wrapped in a transaction)

id	first_name	department	salary
1	Ada	Engineering	120000
2	Bob	Engineering	90000
3	Chen	HR	80000
4	Dee	Sales	110000
5	Eli	Engineering	130000
6	Mia	Marketing	95000

Sample Use Cases (3)

1) Multi-row insert

id	name
1	Engineering
2	HR
3	Sales
4	Ops
5	Legal

2) Insert from SELECT

id	first_name	department_id
1	Ada	1
2	Bob	1
3	Chen	2
4	Dee	3
5	Eli	1

3) Insert order with items

id	customer_id	order_date	total
4	1	2024-06-01	49.99

id	order_id	product_id	qty	price
7	4	2	1	49.99

« Prev: SQL - Create Table Next: SQL - SELECT »