.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 3 October 2026
There are four ways to get rows into a SQL Server table and most people learn one of them and stop. INSERT with a VALUES list covers the first week. INSERT with a SELECT covers most of what comes after, SELECT INTO gets used for things it should not be used for, and bulk loading sits ignored until somebody waits forty minutes for a load that should have taken forty seconds. All four are below, in the order you meet them.
The examples use the same three tables as the rest of the T-SQL tutorials here, so you can run them in sequence:
CREATE TABLE dbo.customers (
customer_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
company_name NVARCHAR(200) NULL,
country_code CHAR(2) NOT NULL,
created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE TABLE dbo.orders (
order_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES dbo.customers(customer_id),
order_status VARCHAR(20) NOT NULL DEFAULT 'pending',
order_total DECIMAL(10,2) NOT NULL,
discount_pct DECIMAL(5,2) NULL,
placed_at DATETIME2(3) NOT NULL,
shipped_at DATETIME2(3) NULL
);
CREATE TABLE dbo.order_items (
order_item_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
order_id INT NOT NULL REFERENCES dbo.orders(order_id),
sku VARCHAR(40) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL
);Step 1: MSSQL INSERT INTO with an explicit column list
INSERT INTO dbo.customers (email, company_name, country_code)
VALUES ('[email protected]', N'Example Ltd', 'GB');Three columns named, three values supplied, positionally matched. customer_id is an identity column so the engine fills it, and created_at has a default so it fills itself. Any column you leave out gets its default. A nullable column with no default gets NULL. A NOT NULL column with nothing to fall back on gets you an error:
Msg 515, Level 16, State 2
Cannot insert the value NULL into column 'country_code', table 'shop.dbo.customers';
column does not allow nulls. INSERT fails.Now the version with no column list, which every tutorial shows because it's shorter:
INSERT INTO dbo.customers
VALUES ('[email protected]', N'Example Ltd', 'GB', SYSUTCDATETIME());That statement is correct today and broken the moment someone changes the table. An ALTER TABLE that adds a column gets you Msg 213, "Column name or number of supplied values does not match table definition", which is the good outcome because it fails loudly. The bad outcome is a rebuild that swaps the order of two columns with compatible types. Country codes start landing in the company name and nothing complains at all. An MSSQL insert column list is four seconds of typing. Write it.
The full grammar is in Microsoft's INSERT reference, hints and the seldom-used OPENROWSET target form included.
Step 2: multi-row VALUES and the 1,000-row limit
One VALUES clause, several row constructors, one trip to the server:
INSERT INTO dbo.order_items (order_id, sku, quantity, unit_price)
VALUES (4821, 'LD-RAM-32', 2, 129.00),
(4821, 'LD-NVME-1T', 1, 89.50),
(4822, 'LD-RAM-32', 4, 129.00);Faster than three separate statements, and it's one transaction, so all three rows land or none do. Then somebody generates a 5,000-row script out of a spreadsheet and hits this:
Msg 10738, Level 15, State 1
The number of row value expressions in the INSERT statement exceeds
the maximum allowed number of 1000 row values.The table value constructor workaround
The limit is narrower than it looks. Microsoft's wording on the table value constructor: "When used as the VALUES clause of an INSERT ... VALUES statement, there is a limit of 1,000 rows. Error 10738 is returned if the number of rows exceeds the maximum." Used as a derived table in a FROM clause instead, there's no limit at all:
INSERT INTO dbo.order_items (order_id, sku, quantity, unit_price)
SELECT v.order_id, v.sku, v.quantity, v.unit_price
FROM (VALUES
(4821, 'LD-RAM-32', 2, 129.00),
(4821, 'LD-NVME-1T', 1, 89.50),
(4822, 'LD-RAM-32', 4, 129.00)
) AS v (order_id, sku, quantity, unit_price);Same literal rows, one extra line of wrapper, error 10738 gone. Error numbers like 10738 and 515 are the fastest search term you have. Common SQL Server errors collects the ones that come up regularly. The wrapper also gives you somewhere to hang a WHERE or a JOIN, which helps when the generated list needs filtering against what's already in the table.
Past a few thousand rows I stop generating INSERT scripts altogether. Producing a 40 MB .sql file to load a CSV is a lot of work for the parser, and the tools in the last section exist for exactly this.
Step 3: INSERT SELECT in MSSQL
The source is a query. Everything else is identical:
CREATE TABLE dbo.orders_archive (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
order_status VARCHAR(20) NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
placed_at DATETIME2(3) NOT NULL
);
INSERT INTO dbo.orders_archive (order_id, customer_id, order_status, order_total, placed_at)
SELECT order_id, customer_id, order_status, order_total, placed_at
FROM dbo.orders
WHERE placed_at < '20250101';Column count and order have to line up between the two lists, and the types have to be compatible. Compatible does a lot of work in that sentence. Precedence rules, which data types, CAST and CONVERT lays out in order, decide who converts to what, and they matter here because SQL Server will happily convert a varchar into an int for you and then fail at runtime on the first row that contains a letter.
MSSQL insert with select for a subset of columns
You don't have to supply every column. This inserts placeholder orders for a set of customers and leaves five columns to their defaults:
INSERT INTO dbo.orders (customer_id, order_status, order_total, placed_at)
SELECT customer_id, 'pending', 0.00, SYSUTCDATETIME()
FROM dbo.customers
WHERE country_code = 'GB';Constants and expressions sit in the SELECT list alongside real columns, which is how you stamp a batch of rows with a load timestamp or a source-system marker without a follow-up UPDATE.
Identity columns and SET IDENTITY_INSERT
Note that dbo.orders_archive.order_id above is a plain INT with no IDENTITY property. That's deliberate. An archive table needs to keep the original keys, and an identity column refuses supplied values:
Msg 544, Level 16, State 1
Cannot insert explicit value for identity column in table 'orders_archive'
when IDENTITY_INSERT is set to OFF.If the target really does have an identity column and you need to preserve source keys, turn the guard off around the statement:
SET IDENTITY_INSERT dbo.orders_archive ON;
INSERT INTO dbo.orders_archive (order_id, customer_id, order_status, order_total, placed_at)
SELECT order_id, customer_id, order_status, order_total, placed_at
FROM dbo.orders
WHERE placed_at < '20250101';
SET IDENTITY_INSERT dbo.orders_archive OFF;Two rules come with it. The column list is mandatory while IDENTITY_INSERT is ON, and only one table per session can have it enabled at a time, so forgetting to switch it off leaves the next table you try to load erroring with Msg 8107. Turn it off in the same batch you turned it on.
Step 4: MSSQL SELECT INTO and what it doesn't copy
SELECT INTO creates the destination table from the shape of the result set and fills it in one statement:
SELECT order_id, customer_id, order_total, placed_at
INTO dbo.orders_2025_snapshot
FROM dbo.orders
WHERE placed_at >= '20250101'
AND placed_at < '20260101';Nothing to declare in advance, which is why it's the fastest way to take a copy of something before you break it. It's also minimally logged under the simple and bulk-logged recovery models, so it moves large result sets quickly.
Microsoft's SELECT INTO documentation is where the list below comes from. What arrives in the new table is column names, data types and nullability. Here is what stays behind:
- Indexes, constraints and triggers. Verbatim: "Indexes, constraints, and triggers defined in the source table aren't transferred to the new table, nor can you specify them." No primary key, no unique constraint, no foreign key, no defaults.
- Computed columns arrive as static values. The definition is gone and the numbers stop updating, which is a nasty surprise in a snapshot table somebody later starts trusting.
- Partitioning. "You can't use SELECT...INTO to create a partitioned table."
- The IDENTITY property, in several cases: a JOIN, a UNION, an expression applied to the identity column or a remote source all strip it. A simple single-table SELECT keeps it, which catches people the other way round when their copy of the table refuses an explicit key value.
- Row order. An ORDER BY in a SELECT INTO does not guarantee the physical order rows are inserted in.
One more, and it's the one that produces confused messages in a support queue: if the statement fails partway through, "the new (empty) table remains." You're left with a table that exists, has the right columns and holds nothing. Re-running the statement then fails with "There is already an object named ... in the database", so drop the empty shell first.
SELECT INTO versus INSERT INTO ... SELECT
The difference is one line. SELECT INTO creates the target and will not write to an existing table. INSERT INTO ... SELECT requires the target to exist already and never creates anything.
Lifespan decides it for me. If the table dies at the end of the session or the end of the day (a snapshot before a migration, a working copy for an investigation, a scratch table in tempdb), SELECT INTO, because writing out a CREATE TABLE for something disposable is wasted effort. If the table will still be there next month, write the CREATE TABLE by hand with the keys, the constraints and the indexes you want, then fill it with INSERT INTO ... SELECT. A permanent table whose types were inferred by a query optimizer is a table nobody can explain six months later.
Capture generated identity values with OUTPUT INSERTED
Insert an order, then insert its lines: you need the new order_id. The OUTPUT clause returns it, and it's the only approach that works for multi-row inserts.
DECLARE @new_orders TABLE (order_id INT, customer_id INT);
INSERT INTO dbo.orders (customer_id, order_status, order_total, placed_at)
OUTPUT inserted.order_id, inserted.customer_id INTO @new_orders
SELECT customer_id, 'pending', 0.00, SYSUTCDATETIME()
FROM dbo.customers
WHERE country_code = 'GB';
SELECT * FROM @new_orders;inserted.* gives you every column of every new row, generated keys and applied defaults included. deleted. is empty for an INSERT, which is expected and not a bug.
SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT
For a single-row insert the classic approach still works, and there are three functions competing for the job:
INSERT INTO dbo.orders (customer_id, order_total, placed_at)
VALUES (91, 249.50, SYSUTCDATETIME());
SELECT SCOPE_IDENTITY() AS new_order_id;SCOPE_IDENTITY()returns the last identity generated in your session and your scope. Use this one.@@IDENTITYreturns the last identity generated in your session in any scope. An AFTER INSERT trigger ondbo.ordersthat writes a row into an audit table with its own identity column hands you that trigger's key and never your order's. The bug appears the day someone adds the trigger, months after the code was written.IDENT_CURRENT('dbo.orders')returns the last identity for that table across every session and scope. Under any concurrency it returns somebody else's row.
SCOPE_IDENTITY for one row, OUTPUT for many. IDENT_CURRENT has a legitimate use in checking where a sequence has got to, and none at all inside application code.
OUTPUT restrictions
OUTPUT with no INTO target fails if the table has enabled triggers for that action, which is the usual reason a statement that worked yesterday breaks after somebody adds auditing. The OUTPUT clause page carries the full list of restrictions. The INTO target itself can't have triggers, can't sit on either side of a foreign key relationship, can't carry CHECK constraints and can't have enabled rules. Rows come back to the client even when the statement subsequently errors and rolls back, so read the output as what the statement attempted. Row order into an OUTPUT INTO table is not guaranteed either.
INSERT with DEFAULT and DEFAULT VALUES
The DEFAULT keyword asks for the column's default explicitly, which is useful when you want a placeholder in a positional list:
INSERT INTO dbo.orders (customer_id, order_status, order_total, placed_at)
VALUES (91, DEFAULT, 0.00, SYSUTCDATETIME());That takes 'pending' from the column's default constraint. With no default defined, DEFAULT means NULL, and on a NOT NULL column it's an error.
When every column can supply its own value, DEFAULT VALUES inserts a whole row with no value list at all:
CREATE TABLE dbo.job_run (
job_run_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
started_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
status VARCHAR(20) NOT NULL DEFAULT 'running'
);
INSERT INTO dbo.job_run DEFAULT VALUES;
SELECT SCOPE_IDENTITY() AS job_run_id;Two lines to claim a job slot and get its id back. I use this shape for anything that logs "something started at some point", and it beats spelling out values the table already knows.
DEFAULT VALUES is one of those bits of syntax I only remember because it looks wrong. The first time I read it I assumed the parser wanted a value list after it and spent a minute hunting for the missing brackets. There is nothing missing.
