SQL INSERT INTO: Syntax, Usage, and Examples

The SQL INSERT INTO statement is used to add new records to a table in a database. It can insert data into all columns or just selected columns, and it supports inserting static values or data from another query.

How to Use INSERT INTO in SQL

The basic syntax of the insert into SQL command is straightforward. You define the target table and then supply the values you want to insert.

Insert Into All Columns

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

You must match the order and count of values with the table's column structure.

Insert Into Specific Columns

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

This version lets you omit columns with default values or auto-generated data, like IDs or timestamps.

When to Use INSERT INTO in SQL

The SQL insert into statement comes in handy for many common database tasks. It’s useful in scenarios like data seeding, copying data between tables, or storing user inputs.

Adding New Records Manually

One of the most common uses is adding a new row of data to a table, especially during initial testing or manual updates.

INSERT INTO users (first_name, last_name, email)
VALUES ('Leila', 'Santos', 'leila@example.com');

This simple command adds a user to the users table.

Inserting Default and Auto-Increment Values

When your table includes columns like id or created_at that automatically generate values, you can omit them during insertion.

INSERT INTO products (name, price)
VALUES ('Notebook', 12.99);

The database fills in the id and created_at fields automatically.

Batch Inserting Multiple Rows

You can insert several rows in one go to reduce roundtrips to the database.

INSERT INTO colors (name, hex)
VALUES
  ('Red', '#FF0000'),
  ('Green', '#00FF00'),
  ('Blue', '#0000FF');

This is more efficient than multiple single-row inserts.

Examples of INSERT INTO SQL

Example 1: insert into table SQL with all columns

Assume a students table with columns: id, name, and grade.

INSERT INTO students
VALUES (1, 'Amina', 'B+');

This inserts a full row using all the columns in order.

Example 2: insert into statement in SQL with specific columns

INSERT INTO students (name, grade)
VALUES ('Noah', 'A');

In this case, id might be auto-generated.

Example 3: SQL insert into table using variables (in procedural SQL)

DECLARE @firstName NVARCHAR(50) = 'Kai';
DECLARE @lastName NVARCHAR(50) = 'Nguyen';

INSERT INTO employees (first_name, last_name)
VALUES (@firstName, @lastName);

This is common in stored procedures or SQL scripts with dynamic data.

Example 4: SQL insert into select from another table

To copy data from one table to another:

INSERT INTO archive_orders (order_id, customer_id, order_date)
SELECT id, customer_id, created_at
FROM orders
WHERE created_at < '2023-01-01';

This version of sql insert into select is powerful for archiving, backups, or bulk migrations.

Example 5: Using SQL insert into table with expressions

You can insert calculated or default values using expressions:

INSERT INTO invoices (customer_id, amount, created_at)
VALUES (123, 250 * 1.2, GETDATE());

Here, the amount is multiplied and the current date is inserted dynamically.

Learn More About INSERT INTO in SQL

How SQL Handles Data Types

When using sql insert into table, values must match the column’s data type. Inserting a string into a numeric field, for instance, will trigger an error. Most databases will try to convert values where possible, but it’s best to be precise.

Example:

-- Correct
INSERT INTO expenses (amount) VALUES (99.95);

-- Problematic (in most databases)
INSERT INTO expenses (amount) VALUES ('ninety-nine');

In strict mode, the second insert will fail.

insert into table SQL with NULL values

You can insert NULL into columns where values are missing or not applicable.

INSERT INTO reviews (user_id, comment, rating)
VALUES (5, NULL, 4);

This is useful when optional fields don’t have a value yet.

insert into statement in SQL with default values

If your table has default values defined, you can omit columns to let the database apply them automatically.

INSERT INTO users (username)
VALUES ('skywalker');

Assuming the role column has a default of 'user', it will be set without needing to specify it.

You can also use the DEFAULT keyword explicitly:

INSERT INTO users (username, role)
VALUES ('mace', DEFAULT);

Using insert into SQL with subqueries

Subqueries can be used in place of static values:

INSERT INTO leaderboard (player_id, score)
VALUES (
  (SELECT id FROM players WHERE username = 'Zara'),
  9800
);

This dynamic approach makes insert into statement in SQL more flexible in real-time applications.

SQL insert into select: Combining Datasets

In data warehousing or reporting systems, it’s common to populate tables using sql insert into select logic.

INSERT INTO top_customers (customer_id, total_spent)
SELECT customer_id, SUM(amount)
FROM purchases
GROUP BY customer_id
HAVING SUM(amount) > 1000;

This moves only high-value customers into a specialized table.

SQL insert into table with joins

You can insert results from a join query:

INSERT INTO order_summaries (order_id, customer_name)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

This lets you copy or transform relational data in bulk.

Validating Before Inserting

In more complex systems, insert into SQL often follows a validation check:

IF NOT EXISTS (
  SELECT 1 FROM users WHERE email = 'hello@example.com'
)
INSERT INTO users (email, registered_at)
VALUES ('hello@example.com', GETDATE());

This pattern avoids inserting duplicates.

Insert and Return Inserted Row (RETURNING)

In databases like PostgreSQL, you can return data from the inserted row:

INSERT INTO customers (name, email)
VALUES ('Lina', 'lina@example.com')
RETURNING id, created_at;

This is especially helpful after inserting user-generated data.

In SQL Server, you can use OUTPUT:

sql
CopyEdit
INSERT INTO users (username)
OUTPUT inserted.id
VALUES ('solana_dev');

Inserting JSON or Complex Data

Some modern databases allow you to insert JSON structures:

INSERT INTO logs (metadata)
VALUES ('{"event":"login","success":true}');

This use case is common in logging or API response storage.

The SQL INSERT INTO command is one of the most frequently used tools in any database developer’s toolkit. From basic single-row inserts to powerful bulk operations using sql insert into select, it enables consistent and flexible data entry into tables. By mastering this command and understanding how to use it with subqueries, expressions, and defaults, you'll handle everything from initial data setup to real-time input handling with confidence.