Home / SQL / INSERT INTO

INSERT INTO

Overview

The INSERT INTO statement is used to insert new records into a table. It allows you to add one or more rows of data to a database table.

INSERT INTO is one of the fundamental data manipulation statements in SQL, along with SELECT, UPDATE, and DELETE.

Basic Syntax

There are two main ways to insert data:

Method 1: Specify Column Names

INSERT with Column Names

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

Method 2: Insert into All Columns

INSERT without Column Names

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Note: Method 2 requires values for all columns in the same order as they appear in the table definition.

Examples

Example 1: Insert Single Record

Insert a new customer

INSERT INTO Customers (customer_id, name, email, city)
VALUES (101, 'John Doe', 'john@example.com', 'New York');

Example 2: Insert without Column Names

Insert with all columns

INSERT INTO Customers
VALUES (102, 'Jane Smith', 'jane@example.com', 'Los Angeles', 'USA');

Example 3: Insert Multiple Records

Insert multiple rows

INSERT INTO Customers (customer_id, name, email, city)
VALUES 
    (103, 'Bob Johnson', 'bob@example.com', 'Chicago'),
    (104, 'Alice Brown', 'alice@example.com', 'Houston'),
    (105, 'Charlie Wilson', 'charlie@example.com', 'Phoenix');

Example 4: Insert with Some Columns

You can insert values for only some columns (others will use default values or NULL):

Partial column insertion

INSERT INTO Customers (name, email)
VALUES ('David Lee', 'david@example.com');

INSERT INTO ... SELECT

You can insert data from another table using SELECT:

Insert from another table

INSERT INTO Customers (name, email, city)
SELECT name, email, city
FROM TempCustomers
WHERE active = 1;

Data Types and Formatting

String Values

String values must be enclosed in single quotes:

String values

INSERT INTO Customers (name, email)
VALUES ('John Doe', 'john@example.com');

Numeric Values

Numeric values do not need quotes:

Numeric values

INSERT INTO Products (product_id, product_name, price)
VALUES (1, 'Laptop', 999.99);

Date Values

Date values are typically enclosed in quotes (format varies by database):

Date values

INSERT INTO Orders (order_id, customer_id, order_date)
VALUES (1, 101, '2024-01-15');

-- Or use database-specific date functions
INSERT INTO Orders (order_id, customer_id, order_date)
VALUES (2, 102, GETDATE()); -- SQL Server
-- VALUES (2, 102, NOW()); -- MySQL
-- VALUES (2, 102, CURRENT_DATE); -- PostgreSQL

NULL Values

Use NULL keyword for missing values (if column allows NULL):

NULL values

INSERT INTO Customers (name, email, phone)
VALUES ('John Doe', 'john@example.com', NULL);

Auto-Increment Columns

For auto-increment columns (like ID fields), you can omit them from INSERT:

Auto-increment columns

-- customer_id is auto-increment, so omit it
INSERT INTO Customers (name, email, city)
VALUES ('John Doe', 'john@example.com', 'New York');

Default Values

Columns with default values will use the default if not specified:

Using default values

-- status has default value 'Active'
INSERT INTO Customers (name, email)
VALUES ('John Doe', 'john@example.com');
-- status will be set to 'Active' automatically

Constraints and Errors

INSERT statements must satisfy table constraints:

PRIMARY KEY: Must be unique and not NULL
NOT NULL: Must provide a value
FOREIGN KEY: Referenced value must exist
UNIQUE: Value must be unique in the column
CHECK: Value must meet the check condition

Error example - duplicate primary key

-- This will fail if customer_id 101 already exists
INSERT INTO Customers (customer_id, name, email)
VALUES (101, 'John Doe', 'john@example.com');

Best Practices

Always specify column names: More explicit and less error-prone
Use parameterized queries: Prevents SQL injection in applications
Validate data first: Check data before inserting
Handle errors: Check for constraint violations and handle appropriately
Use transactions: For multiple inserts, wrap in a transaction
Test with SELECT: After inserting, verify with SELECT

Common Use Cases

Adding new customer records
Creating product entries
Logging transactions or events
Importing data from other sources
Populating lookup tables

Performance Tips

Batch inserts: Insert multiple rows in one statement when possible
Use bulk insert: For large datasets, use bulk insert operations
Index considerations: Too many indexes can slow down inserts
Transaction size: Keep transaction sizes reasonable

Next Steps

Learn about updating existing records with UPDATE, or explore how to handle NULL Values in your database.

← And Or Not Null Values →