INSERT

INSERT

What it means

INSERT adds one or more new rows to a table. You can specify column names explicitly (recommended — resilient to future schema changes) or rely on column order (fragile), insert a single row or multiple rows in one statement, or insert the results of a SELECT query directly.

Examples

INSERT INTO Customers (Name, Email)
VALUES ('Alice', 'alice@example.com');

-- Multiple rows in one statement
INSERT INTO Customers (Name, Email)
VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

-- Insert from a query result (copy filtered data into another table)
INSERT INTO ArchivedOrders (Id, CustomerId, Amount)
SELECT Id, CustomerId, Amount FROM Orders WHERE OrderDate < '2020-01-01';

-- Capture the generated identity value
INSERT INTO Customers (Name, Email) VALUES ('Carol', 'carol@example.com');
SELECT SCOPE_IDENTITY();   -- the auto-generated Id for the row just inserted

Common mistake

Omitting the column list and relying on positional order — this compiles fine today, but silently breaks (or worse, inserts data into the wrong columns without error) the moment someone adds, removes, or reorders a column.

-- Fragile: relies on the table's exact current column order
INSERT INTO Customers VALUES ('Alice', 'alice@example.com');

-- Robust: explicit column names, resilient to future schema changes
INSERT INTO Customers (Name, Email) VALUES ('Alice', 'alice@example.com');

Quick practice

1. Why is specifying an explicit column list generally safer than relying on positional VALUES?

   Answer

   It's resilient to schema changes — if a column is added, removed, or reordered later, an explicit column list still inserts values into the correct places, while positional inserts can silently break or insert wrong data.

2. What does SCOPE_IDENTITY() return after an INSERT into a table with an IDENTITY column?

   Answer

   The auto-generated identity value of the row just inserted, in the current session/scope.

3. Can you insert the results of a SELECT query directly into another table?

   Answer

   Yes — INSERT INTO TargetTable (columns) SELECT columns FROM SourceTable WHERE ...