To insert a new record with SQL, you use the INSERT INTO statement with a table name, a list of columns, and the values you want to add.

Basic syntax

sql

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

This adds one new row to table_name, matching each value to the corresponding column.

Example

sql

INSERT INTO Customers (customer_id, first_name, last_name, age, country)
VALUES (7, 'Ron', 'Weasley', 31, 'UK');

This inserts a new customer with ID 7 into the Customers table.

Insert into all columns

If you are providing values for every column in order, you can omit the column list (not recommended in large systems, but valid).

sql

INSERT INTO Customers
VALUES (8, 'Harry', 'Potter', 30, 'UK');

You must follow the exact column order defined in the table, and the number of values must match the number of columns.

Insert multiple rows at once

You can insert several records in a single statement by listing multiple value sets.

sql

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES
  (1002, 'Jane', 'Smith', '2023-06-16', 55000.00),
  (1003, 'Mike', 'Johnson', '2023-06-17', 52000.00),
  (1004, 'Emily', 'Brown', '2023-06-18', 53000.00);

Each parentheses group represents one new row.

Insert from another table

You can also insert new records by copying from another table using INSERT INTO ... SELECT.

sql

INSERT INTO New_Customers (ID, NAME, AGE)
SELECT ID, NAME, AGE
FROM Customers
WHERE AGE > 30;

This inserts rows into New_Customers based on data selected from Customers.

Key points and best practices

  • Always match the number and order of values to the columns.
  • Prefer explicitly listing column names, to avoid errors if the table structure changes.
  • Respect constraints like NOT NULL, UNIQUE, and primary keys; otherwise the insert will fail.
  • Use parameterized queries in real applications to avoid SQL injection when values come from user input.

TL;DR: Use
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
to add a new record, making sure columns and values line up correctly.

Information gathered from public forums or data available on the internet and portrayed here.