Inserting Data into SQL Server

This tutorial covers the fundamental methods for inserting data into your SQL Server tables. Understanding how to populate your database is a crucial step in data management.

The `INSERT INTO` Statement

The most common way to add new rows to a table is by using the INSERT INTO statement. You can specify which columns to insert data into, or insert values for all columns.

Inserting Values into Specific Columns

This syntax allows you to provide values for a subset of the table's columns. Ensure the order of values matches the order of columns specified.


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

Example: Inserting a new customer

Let's assume we have a Customers table with columns CustomerID, FirstName, LastName, and Email. We want to add a new customer.


INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Jane', 'Doe', 'jane.doe@example.com');

Note that we didn't specify CustomerID, assuming it's an auto-incrementing primary key.

Inserting Values for All Columns

If you are providing values for every column in the table, you can omit the column list. The values must be provided in the exact order the columns appear in the table definition.


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

Example: Inserting a new product

Consider a Products table with ProductID (auto-increment), ProductName, Price, and StockQuantity.


INSERT INTO Products
VALUES ('Laptop', 1200.50, 50);

Inserting Multiple Rows

You can insert multiple rows with a single INSERT INTO statement by separating the value sets with commas.


INSERT INTO table_name (column1, column2)
VALUES
    (value1_row1, value2_row1),
    (value1_row2, value2_row2),
    (value1_row3, value2_row3);

Example: Inserting multiple orders

For an Orders table with OrderID (auto-increment), CustomerID, and OrderDate.


INSERT INTO Orders (CustomerID, OrderDate)
VALUES
    (101, '2023-10-26'),
    (102, '2023-10-26'),
    (101, '2023-10-27');

Inserting Data from Another Table

The INSERT INTO ... SELECT statement allows you to insert data from one table into another. This is incredibly useful for data migration or creating backups.


INSERT INTO destination_table (column1, column2, ...)
SELECT columnA, columnB, ...
FROM source_table
WHERE condition;

Example: Copying active customers

Suppose you have an AllCustomers table and want to copy only those with an IsActive flag set to true into a new ActiveCustomers table.


INSERT INTO ActiveCustomers (CustomerID, FirstName, LastName, Email)
SELECT CustomerID, FirstName, LastName, Email
FROM AllCustomers
WHERE IsActive = 1;

Important Considerations

Ensure data types match between the values you're inserting and the column definitions.
Handle NULL values appropriately. If a column allows NULL and you don't provide a value, it will be set to NULL.
Be mindful of primary key and unique constraints to avoid errors.
For large data insertions, consider performance implications and use bulk insert operations if available.

Next Steps

Now that you know how to insert data, you'll likely want to retrieve it. Proceed to the Querying Data tutorial.

SQL Server Documentation