INSERT with specific columns in SQL

Introduction

We use INSERT with specific columns to add new data to only certain parts of a table, not all columns. This helps when we don't have values for every column.

When you want to add a new user but only know their name and email, not their phone number.

When inserting a new product with just its name and price, leaving other details empty.

When adding a record where some columns have default values and you don't want to specify them.

When you want to avoid errors by specifying exactly which columns you are filling.

Syntax

SQL

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

You must list columns in the same order as the values you provide.

Columns not listed will get their default value or NULL if allowed.

Examples

Adds a new user with only name and email filled.

SQL

INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');

Adds a product with name and price, other columns stay default.

SQL

INSERT INTO products (product_name, price) VALUES ('Book', 9.99);

Inserts an employee record specifying only first and last names.

SQL

INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');

Sample Program

Creates a students table with four columns. Inserts a student with name and age only. City uses default 'Unknown'. Then selects all rows.

SQL

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50) DEFAULT 'Unknown'
);

INSERT INTO students (name, age) VALUES ('Emma', 20);

SELECT * FROM students;

Important Notes

If you omit columns that do not allow NULL and have no default, the INSERT will fail.

Always check the table schema to know which columns are required.

Using specific columns helps avoid mistakes when table structure changes.

Summary

INSERT with specific columns lets you add data to chosen columns only.

You list columns and matching values in order.

Unlisted columns get default values or NULL if allowed.