MariaDB Tutorial / Insert One Row or Multiple Rows with INSERT

INSERT adds new rows to a table. List the destination columns explicitly and provide one or more parenthesized value lists after VALUES, then verify both the number of inserted rows and the values actually stored.

Match each input value to its destination column

Each product is one row, with its identifier, name, category, price, stock and note in separate columns. INSERT matches values to the listed columns by position, not by the names or meanings of the values. A price and a stock quantity are both numbers, but swapping them still changes the record’s meaning. Inspect the destination structure using Define a Table Structure with CREATE TABLE, then use the column projections in Select Columns, Aliases, Calculations, and DISTINCT to verify inserted values. Loading many rows from a file also requires format validation as shown in Import and Export CSV with Correct Paths, Types, and Encoding.

Omitting the column list makes the statement depend on the table’s complete column order. Explicit lists are easier to review when a schema changes. A literal apostrophe can be doubled, as in 'O''Brien'; applications accepting outside input should use parameterized queries rather than concatenate SQL strings.

Check the MariaDB documentation for the syntax and conditions used here: INSERT.

Run the complete single-row and multi-row example

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_13_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

Finish the table definition before inserting rows. The first INSERT adds one product and the second adds the remaining five. The first six identifiers are explicit so the results are reproducible; automatic identifiers are examined separately. The CREATE TABLE guide explains the definition.

USE shopcourse;
CREATE TABLE products_13_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p13_price CHECK (price >= 0),
  CONSTRAINT ck_p13_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_13_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL);
SELECT ROW_COUNT() AS inserted_rows;
INSERT INTO products_13_lab
  (product_id, product_name, category, price, stock, note)
VALUES
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');
SELECT ROW_COUNT() AS inserted_rows;

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

The two immediate ROW_COUNT() results are 1 and 5. Read this value directly after the write, because another statement changes what counts as the most recent statement. With autocommit enabled, the two INSERT statements can commit separately: failure of the second does not automatically undo the first.

Read back the stored rows

SELECT product_id, product_name, category, price, stock, note FROM products_13_lab ORDER BY product_id;

The product and note descriptions in this table are English glosses of the SQL’s original Korean strings. Compare columns and values instead of relying only on a success message. Use note IS NULL if the client displays NULL and an empty note similarly. If text is garbled, check the UTF-8 file and client character set before inserting it again.

Inspect generated identifiers and omitted-column defaults

Run this entire block in one connection. It omits the identifier, stock and note, checks the new row, then rolls it back. On the untouched fresh fixture, generated_id is 7; after other attempts, focus on the actual generated value and defaults instead of requiring the same number.

START TRANSACTION;
INSERT INTO products_13_lab (product_name, category, price)
VALUES ('연습 상품', '문구', 2000.00);
SELECT LAST_INSERT_ID() AS generated_id;
SELECT product_id, stock, note IS NULL AS note_is_null
FROM products_13_lab WHERE product_id = LAST_INSERT_ID();
ROLLBACK;
SELECT COUNT(*) AS remaining_rows FROM products_13_lab;

The omitted stock receives DEFAULT 0 and the omitted note receives DEFAULT NULL. Explicitly supplying NULL to a NOT NULL column differs from omission; the NULL and default-value guide explains why. ROLLBACK restores the row count to six but does not guarantee that the next automatic identifier will reuse 7.

Test invalid inputs separately

After the six valid rows, execute the following statements one at a time. They respectively reuse primary key 1, provide one value for two listed columns, and supply NULL for the required product name.

INSERT INTO products_13_lab (product_id, product_name, category, price)
VALUES (1, '중복 상품', '문구', 1000);

INSERT INTO products_13_lab (product_name, price) VALUES ('값 부족');

INSERT INTO products_13_lab (product_name, category, price)
VALUES (NULL, '문구', 1000);

Expect duplicate-key, column-count and cannot-be-NULL errors. After each, verify SELECT COUNT(*) FROM products_13_lab; still returns six. Keep deliberate failures out of the normal setup file, because client error-handling options may stop later statements. Fix the cause instead of masking it with INSERT IGNORE or REPLACE, which can have different data-changing effects.

Check warnings and repeat only the disposable exercise

SQL modes affect invalid and out-of-range inputs. These prices and stock quantities are within their declared ranges; if a warning occurs, run SHOW WARNINGS immediately after the relevant statement. A multi-row statement can reduce network round trips but is still subject to statement-size and other limits. Bulk imports need a separate workflow.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_13_lab;