The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

INSERT: Add rows explicitly

When we register a customer, an order or an inventory movement we need to create new rows. INSERT indicates the destination table, the columns that we will receive and the values ​​that will be stored.

Explicitly listing columns is important: it makes the intent visible and avoids relying on the physical order of the table.

Syntax

INSERT INTO dbo.Tabla
    (Columna1, Columna2, Columna3)
VALUES
    (Valor1, Valor2, Valor3);

The correspondence is positional: Columna1 receives Valor1, Columna2 receives Valor2, and so on. The number of values must match the number of listed columns.

Example: insert a row

INSERT INTO dbo.Categoria (Nombre)
VALUES (N'Audio');

We do not include IdCategoria because it is an IDENTITY column: SQL Server automatically generates a new number for each row. We also do not need to list every column when the omitted ones accept NULL—an absent or unknown value—or have a default value defined by the table.

Insert multiple rows

A single statement can contain multiple groups of values:

INSERT INTO dbo.Categoria (Nombre)
VALUES
    (N'Redes'),
    (N'Impresión'),
    (N'Energía');

All rows must provide the values ​​in the same order as the column list.

Insert the result of a query

We can also copy or transform data from another set:

In the example, SUM collects the line item amounts and GROUP BY produces one row per order. The result of that query must have the same number and order of columns as the list in INSERT.

INSERT INTO dbo.PedidoHistorico
    (IdPedidoOriginal, IdCliente, FechaPedido, Estado, Total)
SELECT
    p.IdPedido,
    p.IdCliente,
    p.FechaPedido,
    p.Estado,
    SUM(pd.Cantidad * pd.PrecioUnitario * (1 - pd.Descuento / 100.0))
FROM dbo.Pedido AS p
INNER JOIN dbo.PedidoDetalle AS pd
    ON pd.IdPedido = p.IdPedido
WHERE p.Estado IN ('Enviado', 'Cancelado')
GROUP BY
    p.IdPedido,
    p.IdCliente,
    p.FechaPedido,
    p.Estado;

It is a good idea to first only run the query to check which rows it will insert.

Confirm what was inserted

OUTPUT allows you to retrieve generated values and affected rows without performing a subsequent search:

INSERT INTO dbo.Categoria (Nombre)
OUTPUT inserted.IdCategoria, inserted.Nombre
VALUES (N'Seguridad');

To capture a single IDENTITY value, we can also use SCOPE_IDENTITY(), which returns the last value generated in the same execution scope. OUTPUT returns data from affected rows and is more flexible when a statement inserts several rows.

What should be checked

Before inserting, we check constraints, types, lengths, and duplicates. A constraint is a rule enforced by the table. UNIQUE prevents repeated values, a foreign key requires a related row, and CHECK validates a condition written in the definition.

When an operation involves several related tables, a transaction groups changes so they can be committed or rolled back as one unit. This prevents us from keeping only part of the change. We also need to decide what should happen if we run the same process twice: insert duplicates, update existing rows, or ignore rows already processed.