INSERT INTO
The simplest way to insert data into a table is by using the
To be more expressive, the code below will insert data into specific columns. I specify both the names of the columns and the values.
INSERT INTO
products
(product_id, product_name, category, price, cost, stock_quantity, supplier_id, launch_date, weight_kg, is_discontinued, rating)
VALUES
(1, 'iPhone 14 Pro', 'Smartphones', 999.99, 650.00, 45, 1, '2022-09-16', 0.21, FALSE, 4.7),
(2, 'Samsung Galaxy S23', 'Smartphones', 799.99, 520.00, 38, 2, '2023-02-01', 0.17, FALSE, 4.5),
(3, 'MacBook Pro 16"', 'Laptops', 2499.99, 1800.00, 12, 1, '2021-10-26', 2.15, FALSE, 4.8),
(4, 'Dell XPS 13', 'Laptops', 1299.99, 900.00, 23, 3, '2022-01-15', 1.27, FALSE, NULL),
(5, 'iPad Air', 'Tablets', 599.99, 400.00, 67, 1, '2022-03-18', 0.46, FALSE, 4.4);Upon successful insertion, the database services you are using will return information such as below, letting you
know the
INSERT 0 5
Now, we can run the query and see the results of our insert
select * from products product_id | product_name | category | price | cost | stock_quantity | supplier_id | launch_date | weight_kg | is_discontinued | rating
------------+--------------------+-------------+---------+---------+----------------+-------------+-------------+-----------+-----------------+--------
1 | iPhone 14 Pro | Smartphones | 999.99 | 650.00 | 45 | 1 | 2022-09-16 | 0.21 | f | 4.7
2 | Samsung Galaxy S23 | Smartphones | 799.99 | 520.00 | 38 | 2 | 2023-02-01 | 0.17 | f | 4.5
3 | MacBook Pro 16" | Laptops | 2499.99 | 1800.00 | 12 | 1 | 2021-10-26 | 2.15 | f | 4.8
4 | Dell XPS 13 | Laptops | 1299.99 | 900.00 | 23 | 3 | 2022-01-15 | 1.27 | f |
5 | iPad Air | Tablets | 599.99 | 400.00 | 67 | 1 | 2022-03-18 | 0.46 | f | 4.4
(5 rows)
INSERT INTO with QUERY
Sometimes you may want to insert values into a table using a query that fetches conditional information from
another table.
INSERT INTO products
SELECT
product_id,
product_name,
category,
price,
cost,
stock_quantity,
supplier_id,
launch_date,
weight_kg,
is_discontinued,
rating
FROM products_dup
;Again this is a valid way to add values but because our products_dup is empty, we will not have any output. In
fact the results of running this is seen below with
returned value of
$ INSERT INTO products
SELECT
product_id,
product_name,
category,
price,
cost,
stock_quantity,
supplier_id,
launch_date,
weight_kg,
is_discontinued,
rating
FROM products_dup
;
INSERT 0 0
$
INSERTING MULTIPLE VALUES
Much like we have done to insert into for 5 values. We can do this for many more. Below is a full script to insert 47 more values.
INSERT INTO products
VALUES
(6, 'AirPods Pro', 'Audio', 249.99, 150.00, 89, 1, '2021-10-26', 0.05, FALSE, 4.6),
(7, 'Sony WH-1000XM4', 'Audio', 349.99, 200.00, 34, 4, '2020-08-06', 0.25, FALSE, 4.7),
(8, 'Nintendo Switch', 'Gaming', 299.99, 180.00, 156, 5, '2017-03-03', 0.88, FALSE, 4.3),
(9, 'PlayStation 5', 'Gaming', 499.99, 350.00, 8, 5, '2020-11-12', 4.5, FALSE, NULL),
(10, 'Xbox Series X', 'Gaming', 499.99, 350.00, 15, 6, '2020-11-10', 4.45, FALSE, 4.5),
(11, 'Apple Watch Series 8', 'Wearables', 399.99, 250.00, 78, 1, '2022-09-16', 0.04, FALSE, 4.2),
(12, 'Samsung 65" QLED TV', 'TV', 1799.99, 1200.00, 19, 2, '2022-04-28', 25.4, FALSE, 4.6),
(13, 'LG OLED 55"', 'TV', 1299.99, 900.00, 14, 7, '2022-02-15', 18.7, FALSE, NULL),
(14, 'Canon EOS R5', 'Camera', 3899.99, 2800.00, 6, 8, '2020-07-09', 0.74, FALSE, 4.8),
(15, 'GoPro Hero 11', 'Camera', 399.99, 250.00, 43, 9, '2022-09-14', 0.15, FALSE, 4.3),
(16, 'DJI Mini 3', 'Drones', 669.99, 450.00, 27, 10, '2022-05-10', 0.25, FALSE, 4.4),
(17, 'Logitech MX Master 3', 'Accessories', 99.99, 60.00, 234, 11, '2019-09-26', 0.14, FALSE, NULL),
(18, 'Mechanical Keyboard RGB', 'Accessories', 149.99, 80.00, 167, 12, '2021-06-20', 1.2, FALSE, 4.1),
(19, 'Monitor 27" 4K', 'Monitors', 449.99, 280.00, 52, 13, '2022-01-10', 5.8, FALSE, 4.5),
(20, 'Webcam HD 1080p', 'Accessories', 79.99, 40.00, 198, 14, '2020-03-15', 0.18, FALSE, 4.0),
(21, 'USB-C Hub', 'Accessories', 49.99, 25.00, 345, 15, '2021-11-08', 0.12, FALSE, NULL),
(22, 'Wireless Charger', 'Accessories', 39.99, 20.00, 289, 16, '2020-09-12', 0.25, FALSE, 3.9),
(23, 'Bluetooth Speaker', 'Audio', 129.99, 70.00, 76, 17, '2021-04-22', 0.68, FALSE, 4.2),
(24, 'Smart Home Hub', 'Smart Home', 199.99, 120.00, 94, 18, '2022-08-15', 0.35, FALSE, 4.1),
(25, 'Security Camera', 'Smart Home', 159.99, 90.00, 118, 19, '2021-12-03', 0.42, FALSE, NULL),
(26, 'Smart Doorbell', 'Smart Home', 249.99, 150.00, 63, 20, '2022-03-25', 0.31, FALSE, 4.3),
(27, 'Fitness Tracker', 'Wearables', 199.99, 120.00, 145, 21, '2022-07-18', 0.03, FALSE, 4.0),
(28, 'VR Headset', 'Gaming', 399.99, 250.00, 29, 22, '2022-10-11', 0.72, FALSE, 4.4),
(29, 'Robot Vacuum', 'Home', 599.99, 350.00, 36, 23, '2021-09-07', 3.8, FALSE, NULL),
(30, 'Air Fryer', 'Kitchen', 129.99, 70.00, 87, 24, '2020-11-20', 4.2, FALSE, 4.2),
(31, 'Coffee Maker', 'Kitchen', 199.99, 110.00, 54, 25, '2021-03-14', 6.5, FALSE, 4.1),
(32, 'Electric Toothbrush', 'Personal Care', 89.99, 45.00, 167, 26, '2022-01-28', 0.15, FALSE, 4.3),
(33, 'Hair Dryer Pro', 'Personal Care', 149.99, 80.00, 78, 27, '2021-08-30', 0.58, FALSE, NULL),
(34, 'Massage Gun', 'Health', 199.99, 120.00, 92, 28, '2022-06-12', 0.95, FALSE, 4.4),
(35, 'Power Bank 20000mAh', 'Accessories', 59.99, 30.00, 234, 29, '2021-05-17', 0.45, FALSE, 4.2),
(36, 'Car Phone Mount', 'Auto', 29.99, 15.00, 456, 30, '2020-07-22', 0.22, FALSE, 3.8),
(37, 'Dash Cam', 'Auto', 119.99, 65.00, 83, 31, '2021-10-14', 0.18, FALSE, NULL),
(38, 'LED Strip Lights', 'Home', 49.99, 25.00, 267, 32, '2022-04-08', 0.35, FALSE, 4.0),
(39, 'Smart Thermostat', 'Smart Home', 299.99, 180.00, 47, 33, '2021-11-25', 0.68, FALSE, 4.5),
(40, 'Outdoor Security Light', 'Smart Home', 89.99, 50.00, 125, 34, '2022-02-19', 1.2, FALSE, 4.1),
(41, 'Gaming Mouse RGB', 'Gaming', 79.99, 40.00, 178, 35, '2021-07-09', 0.12, FALSE, NULL),
(42, 'Gaming Headset', 'Gaming', 159.99, 85.00, 94, 36, '2022-09-02', 0.35, FALSE, 4.3),
(43, 'External SSD 1TB', 'Storage', 149.99, 85.00, 67, 37, '2021-12-16', 0.15, FALSE, 4.6),
(44, 'USB Flash Drive 128GB', 'Storage', 24.99, 12.00, 567, 38, '2020-06-11', 0.01, FALSE, 3.9),
(45, 'Laptop Stand', 'Accessories', 69.99, 35.00, 143, 39, '2021-02-28', 0.88, FALSE, NULL),
(46, 'Document Camera', 'Office', 199.99, 120.00, 38, 40, '2022-05-20', 0.45, FALSE, 4.2),
(47, 'Label Maker', 'Office', 79.99, 40.00, 89, 41, '2021-09-15', 0.62, FALSE, 4.0),
(48, 'Paper Shredder', 'Office', 149.99, 85.00, 23, 42, '2020-12-04', 8.5, FALSE, 4.1),
(49, 'Projector Mini', 'Electronics', 299.99, 180.00, 41, 43, '2022-08-07', 1.2, FALSE, NULL),
(50, 'Smart Scale', 'Health', 79.99, 45.00, 156, 44, '2021-06-30', 1.8, FALSE, 4.0),
(51, 'Electric Kettle', 'Kitchen', 49.99, 25.00, 198, 45, '2020-04-18', 1.1, TRUE, 3.8),
(52, 'Tablet Stand', 'Accessories', 34.99, 18.00, NULL, 46, '2021-11-12', 0.45, FALSE, NULL);This concludes our discussion on the INSERT INTO statement for inserting data. In the next section, we look at how to ALTER/UPDATE TABLES.