- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
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.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.