SQL INSERT INTO

Learn how to add new records to database tables using the SQL INSERT INTO statement.

What is the INSERT INTO Statement?

The INSERT INTO statement is used to add new records to a database table. You can insert a single row, multiple rows, or data selected from another table.

Basic INSERT INTO Syntax

The basic syntax specifies the table name, the columns receiving data, and the values that should be inserted.

CREATE TABLE planets (
  id INTEGER,
  name TEXT,
  type TEXT
);

INSERT INTO planets (id, name, type)
VALUES (1, 'Mars', 'Terrestrial');

SELECT * FROM planets;

Inserting a Single Record

You can add one complete record by providing values for the required columns.

CREATE TABLE museums (
  id INTEGER,
  name TEXT,
  city TEXT
);

INSERT INTO museums (id, name, city)
VALUES (1, 'Science Museum', 'Delhi');

SELECT * FROM museums;

Inserting Multiple Records

Multiple records can be inserted with one INSERT INTO statement by separating each row with a comma.

CREATE TABLE instruments (
  id INTEGER,
  name TEXT,
  family TEXT
);

INSERT INTO instruments (id, name, family)
VALUES
  (1, 'Piano', 'Keyboard'),
  (2, 'Violin', 'Strings'),
  (3, 'Flute', 'Woodwind'),
  (4, 'Drums', 'Percussion');

SELECT * FROM instruments;

Inserting Data into Selected Columns

You can specify only the columns that need values. Columns that are not specified can receive their default value or NULL.

CREATE TABLE apartments (
  id INTEGER,
  owner TEXT,
  city TEXT,
  floor INTEGER
);

INSERT INTO apartments (id, owner, city)
VALUES (101, 'Neel', 'Mumbai');

SELECT * FROM apartments;

INSERT with NULL Values

If a value is unknown or unavailable, NULL can be inserted into a column that allows NULL values.

CREATE TABLE deliveries (
  id INTEGER,
  customer TEXT,
  address TEXT,
  delivered_date TEXT
);

INSERT INTO deliveries (id, customer, address, delivered_date)
VALUES (501, 'Ishita', 'Sector 12', NULL);

SELECT * FROM deliveries;

INSERT with Default Values

A column can have a default value. If that column is omitted from the INSERT statement, SQLite uses the defined default value.

CREATE TABLE tickets (
  id INTEGER,
  subject TEXT,
  status TEXT DEFAULT 'Open'
);

INSERT INTO tickets (id, subject)
VALUES (1001, 'Website login issue');

SELECT * FROM tickets;

INSERT with an Automatically Generated ID

SQLite can automatically generate an integer primary key. This means you do not need to provide the ID manually.

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  name TEXT,
  difficulty TEXT
);

INSERT INTO recipes (name, difficulty)
VALUES ('Vegetable Pasta', 'Easy');

INSERT INTO recipes (name, difficulty)
VALUES ('Mushroom Soup', 'Medium');

SELECT * FROM recipes;

INSERT Text and Numeric Values

SQL allows different types of values to be inserted into their corresponding columns.

CREATE TABLE bicycles (
  id INTEGER,
  model TEXT,
  gears INTEGER,
  price REAL
);

INSERT INTO bicycles (id, model, gears, price)
VALUES (7, 'Trail Rider', 18, 12999.75);

SELECT * FROM bicycles;

INSERT Data from Another Table

The INSERT INTO ... SELECT syntax can copy matching rows from one table into another table.

CREATE TABLE courses (
  id INTEGER,
  name TEXT,
  duration INTEGER
);

INSERT INTO courses (id, name, duration)
VALUES
  (1, 'Physics', 12),
  (2, 'Chemistry', 8),
  (3, 'Mathematics', 15);

CREATE TABLE long_courses (
  id INTEGER,
  name TEXT,
  duration INTEGER
);

INSERT INTO long_courses (id, name, duration)
SELECT id, name, duration
FROM courses
WHERE duration > 10;

SELECT * FROM long_courses;

INSERT OR IGNORE

SQLite’s INSERT OR IGNORE can skip an insert when the new record would violate a UNIQUE or PRIMARY KEY constraint.

CREATE TABLE coupons (
  id INTEGER PRIMARY KEY,
  code TEXT UNIQUE
);

INSERT INTO coupons (id, code)
VALUES (1, 'SAVE10');

INSERT OR IGNORE INTO coupons (id, code)
VALUES (2, 'SAVE10');

SELECT * FROM coupons;

INSERT OR REPLACE

SQLite’s INSERT OR REPLACE can replace an existing row when a PRIMARY KEY or UNIQUE constraint conflicts.

CREATE TABLE profiles (
  id INTEGER PRIMARY KEY,
  username TEXT,
  level INTEGER
);

INSERT INTO profiles (id, username, level)
VALUES (1, 'nova', 5);

INSERT OR REPLACE INTO profiles (id, username, level)
VALUES (1, 'nova', 6);

SELECT * FROM profiles;

INSERT Using SELECT with a Calculated Value

A SELECT query can calculate values before inserting them into another table.

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price REAL
);

INSERT INTO sales (product, quantity, price)
VALUES
  ('Desk Lamp', 4, 850.00),
  ('Wall Clock', 2, 1200.00),
  ('Study Chair', 3, 4500.00);

CREATE TABLE sales_summary (
  product TEXT,
  total_value REAL
);

INSERT INTO sales_summary (product, total_value)
SELECT product, quantity * price
FROM sales;

SELECT * FROM sales_summary;

INSERT with a Condition

When using INSERT INTO ... SELECT, a WHERE clause can control which records are inserted.

CREATE TABLE applicants (
  id INTEGER,
  name TEXT,
  score INTEGER
);

INSERT INTO applicants (id, name, score)
VALUES
  (1, 'Tara', 72),
  (2, 'Mohit', 91),
  (3, 'Zoya', 84),
  (4, 'Arnav', 65);

CREATE TABLE selected_applicants (
  id INTEGER,
  name TEXT,
  score INTEGER
);

INSERT INTO selected_applicants (id, name, score)
SELECT id, name, score
FROM applicants
WHERE score >= 80;

SELECT * FROM selected_applicants;

INSERT INTO Comparison

Method	Purpose	Example
Single row	Insert one record	`VALUES (1, 'Mars')`
Multiple rows	Insert several records	`VALUES (...), (...)`
INSERT … SELECT	Copy query results	`INSERT INTO archive SELECT ...`
INSERT OR IGNORE	Skip conflicting rows	`INSERT OR IGNORE INTO ...`
INSERT OR REPLACE	Replace conflicting rows	`INSERT OR REPLACE INTO ...`

Advantages of INSERT INTO

Adds new records to database tables.
Supports single and multiple row insertion.
Allows specific columns to be populated.
Can copy data from one table to another.
Works with default and NULL values.
SQLite provides additional conflict-handling options.

Best Practices

Specify column names explicitly whenever possible.
Make sure the number and order of values match the selected columns.
Use appropriate data types for inserted values.
Use transactions when inserting many related records.
Check UNIQUE and PRIMARY KEY constraints before inserting data.
Be careful when using INSERT OR REPLACE because it can replace an existing row.

The INSERT INTO statement is one of the fundamental SQL commands for adding data. By mastering single-row, multi-row, and INSERT ... SELECT operations, you can efficiently populate and manage database tables.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.