INSERT and multirow inserts

State the column list and data source explicitly.

UnderstandStudy an examplePractise

Understand the concept

INSERT adds rows. Explicit column lists protect against incorrect matches after schema-order changes. VALUES can insert one or multiple rows; INSERT ... SELECT copies a result into a target. Target types and constraints validate the incoming values.

Missing references, duplicate keys and CHECK violations can fail an insert. OUTPUT can return inserted identifiers to the application. Persistent lab inserts here are rolled back in a transaction; run them only in SqlStudioLab.

WHEN TO USE IT

Use it for new records, staging and copying query results.

COMMON PITFALL

Omitting columns or relying solely on application validation for relationships.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

CREATE TABLE #Names (Name nvarchar(30));
INSERT INTO #Names(Name) VALUES (N'Ada'), (N'Can');
SELECT * FROM #Names;

Inserts two rows in one statement.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Copy names of in-stock products to a temporary table.

Show the solution and explanation

Worked solution

CREATE TABLE #Available (ProductName nvarchar(100)); INSERT INTO #Available(ProductName) SELECT ProductName FROM lab.Product WHERE Stock > 0; SELECT * FROM #Available;

Copies six products; Cable is out of stock.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.