Skip to content

INSERT, UPDATE, DELETE and transactions in T-SQL

Reading data wrong gives you a wrong report; changing data wrong gives you a wrong database. This page covers the three data modification statements (INSERT, UPDATE, DELETE) and the transaction handling that makes several changes succeed or fail together. Each section shows the OUTPUT clause too, because seeing exactly which rows a statement touched is the best habit you can build when writing to production tables.

The samples continue from the OrdersPortal sample data and run in order: the UPDATE sample relies on the rows the INSERT sample adds. No database was available while this page was written, so the samples were not run here; the syntax was checked against the Microsoft Learn T-SQL reference and the expected results were worked out from the data.

Topic Use it when
INSERT You add new rows, from values or from a query.
UPDATE You change column values in existing rows.
DELETE You remove rows, some or all.
Transactions Several changes must succeed or fail as one unit.

INSERT INTO table (column list) VALUES (…) adds rows. A single VALUES clause can carry up to 1,000 row constructors (SQL Server 2008), and INSERT … SELECT copies the result of a query, which is the efficient way to move many rows. Columns you leave out get their DEFAULT, their IDENTITY value, or NULL; a NOT NULL column without a default must be supplied.

For tables with an IDENTITY column, SCOPE_IDENTITY() returns the last identity value generated in the current scope; avoid @@IDENTITY, which a trigger can change. The more general tool is the OUTPUT clause, which returns columns from the inserted pseudo-table for every row the statement added, including identity values and defaults, even for multi-row inserts.

INSERT INTO dbo.Orders (Id, CustomerId, OrderDate, Status, Amount)
OUTPUT inserted.Id, inserted.CustomerId, inserted.Status, inserted.Amount
VALUES (1006, 3, '2026-11-10', 'Pending', 62.00),
(1007, 3, '2026-11-12', 'Pending', 18.40);

Expected result:

Id CustomerId Status Amount
1006 3 Pending 62.00
1007 3 Pending 18.40

Pitfalls:

  • Omitting the column list. INSERT INTO dbo.Orders VALUES (…) depends on the current column order and breaks, or worse silently mis-assigns compatible values, when the table changes. Always list columns.
  • SELECT … INTO versus INSERT … SELECT. SELECT … INTO NewTable creates a new table without the source’s constraints and indexes; use it for scratch copies only.
  • Row-by-row inserts from application loops. Thousands of single-row round trips are slow. Send a table-valued parameter, use SqlBulkCopy/BULK INSERT, or batch rows into one statement.

UPDATE table SET column = expression [, …] WHERE condition changes existing rows. Expressions can use the row’s current values (Amount = Amount * 1.10), and all assignments in one statement see the values from before the statement started. T-SQL also allows UPDATE … FROM with joins, so you can update one table based on another; reference the target by its alias.

The OUTPUT clause exposes both deleted (the values before) and inserted (the values after), which turns an update into its own audit trail. Without a WHERE clause, UPDATE changes every row in the table.

-- Put pending orders from inactive customers on hold, and show what changed
UPDATE o
SET o.Status = 'OnHold'
OUTPUT inserted.Id, deleted.Status AS OldStatus, inserted.Status AS NewStatus
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.Id = o.CustomerId
WHERE c.IsActive = 0
AND o.Status = 'Pending';

Expected result (Chen Wei is inactive and owns the two orders added above):

Id OldStatus NewStatus
1006 Pending OnHold
1007 Pending OnHold

Pitfalls:

  • A forgotten or partial WHERE. Run the same FROM … WHERE as a SELECT first, check the row count, then run the UPDATE inside a transaction you can roll back.
  • Non-deterministic UPDATE … FROM. If the join matches several source rows for one target row, SQL Server picks one value with no guarantee which. Make the join unique, or aggregate the source first.
  • Large updates in one statement can fill the transaction log and escalate to a table lock. Update in batches of a few thousand rows with UPDATE TOP (n) in a loop when the change is big.

DELETE FROM table WHERE condition removes rows. Like UPDATE it supports joins (DELETE o FROM dbo.Orders AS o JOIN …), TOP (n) for batching, and OUTPUT deleted.* to return or archive what was removed. Every deleted row is fully logged, DELETE triggers fire, and foreign keys are checked: deleting a customer who still has orders fails unless the key was declared ON DELETE CASCADE.

TRUNCATE TABLE removes all rows by deallocating pages: it is minimally logged and much faster, resets the identity seed, does not fire DELETE triggers, and is not allowed on a table referenced by a foreign key. Both can be rolled back inside an explicit transaction. DROP TABLE removes the table itself; see Drop and truncate.

-- Archive and remove cancelled orders
DELETE FROM dbo.Orders
OUTPUT deleted.Id, deleted.CustomerId, deleted.Amount
WHERE Status = 'Cancelled';

Expected result:

Id CustomerId Amount
1004 2 300.00

In a real system you would usually write OUTPUT deleted.* INTO dbo.OrdersArchive so the archive insert and the delete happen in one atomic statement.

Pitfalls:

  • DELETE without WHERE. It empties the table. Use the “select first, then delete in a transaction” habit, and restrict delete permissions in production.
  • Huge deletes. Deleting millions of rows in one statement holds locks and grows the log. Delete in batches with DELETE TOP (5000) … WHERE … in a loop, or switch out a partition.
  • Soft delete versus hard delete. If other tables, reports or audits need the row, set an IsDeleted flag or a DeletedAt date instead, and filter it out in views.

A transaction groups statements into one atomic unit: either all changes are committed or none are. Each statement on its own is already atomic (autocommit mode); BEGIN TRANSACTION … COMMIT TRANSACTION extends that to several statements. ROLLBACK TRANSACTION undoes everything since the outermost BEGIN. Transactions also hold locks until they finish, so keep them short and never wait for user input inside one.

By default a run-time error such as a constraint violation rolls back only the failing statement and the transaction carries on, which is rarely what you want. SET XACT_ABORT ON makes any run-time error roll back the whole transaction. Combine it with TRY…CATCH, check XACT_STATE() or @@TRANCOUNT in the CATCH block, roll back, and re-raise the original error with THROW (SQL Server 2012), which also honors XACT_ABORT. The default isolation level is READ COMMITTED; Azure SQL Database turns on the READ_COMMITTED_SNAPSHOT option by default so readers do not block writers.

CREATE TABLE dbo.OrderStatusHistory (
OrderId INT NOT NULL,
OldStatus VARCHAR(20) NOT NULL,
NewStatus VARCHAR(20) NOT NULL,
ChangedAt DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE OR ALTER PROCEDURE dbo.CancelOrder @OrderId int
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.OrderStatusHistory (OrderId, OldStatus, NewStatus)
SELECT Id, Status, 'Cancelled'
FROM dbo.Orders WITH (UPDLOCK)
WHERE Id = @OrderId AND Status <> 'Cancelled';
IF @@ROWCOUNT = 0
THROW 50001, 'Order not found or already cancelled.', 1;
UPDATE dbo.Orders SET Status = 'Cancelled' WHERE Id = @OrderId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW; -- re-raise the original error to the caller
END CATCH;
END;
GO
EXEC dbo.CancelOrder @OrderId = 1003; -- succeeds: history row + status change
EXEC dbo.CancelOrder @OrderId = 1003; -- fails: nothing is changed

Expected result: the first call commits one history row (1003, Pending, Cancelled) and the status change. The second call raises Msg 50001, Level 16, State 1: Order not found or already cancelled. and leaves the data exactly as it was.

Pitfalls:

  • Committing after an error. Without XACT_ABORT ON and a CATCH block, a failed INSERT followed by a successful UPDATE commits half the work.
  • Nested BEGIN TRANSACTION is not a real nested transaction. Inner commits only decrement @@TRANCOUNT; a ROLLBACK anywhere rolls back everything. Use SAVE TRANSACTION for partial rollback.
  • NOLOCK as a fix for blocking. READ UNCOMMITTED reads uncommitted and even duplicated or missing rows. Consider READ_COMMITTED_SNAPSHOT instead, after testing tempdb version-store usage.
Need Use
Copy many rows from a query INSERT … SELECT
Know the generated keys of inserted rows OUTPUT inserted.Id (multi-row) or SCOPE_IDENTITY() (single row)
Empty a staging table fast TRUNCATE TABLE
Remove some rows, keep an archive DELETE … OUTPUT deleted.* INTO Archive
Several changes that must all succeed Transaction with XACT_ABORT ON, TRY…CATCH and THROW
Change millions of rows Batched UPDATE TOP (n) / DELETE TOP (n) loop

What is the difference between DELETE and TRUNCATE TABLE? DELETE removes rows one at a time, can filter with WHERE, logs every row, fires triggers and keeps the identity seed. TRUNCATE removes all rows by deallocating pages, is minimally logged, resets the identity, does not fire DELETE triggers and cannot run on a table referenced by a foreign key. Both can be rolled back inside a transaction.

Why use SCOPE_IDENTITY() or OUTPUT instead of @@IDENTITY? @@IDENTITY returns the last identity generated in the session in any scope, so a trigger that inserts into an audit table changes it. SCOPE_IDENTITY() is limited to the current scope, and OUTPUT inserted.Id returns every generated value for multi-row inserts.

What does SET XACT_ABORT ON change? It makes any run-time error terminate the batch and roll back the open transaction, instead of rolling back only the failing statement. It is recommended in procedures that modify data and is required for most distributed transactions.

What are the four ACID properties and how does SQL Server guarantee durability? Atomicity, consistency, isolation and durability. Durability comes from write-ahead logging: log records are hardened to the transaction log on disk before a commit is acknowledged, and crash recovery redoes or undoes work from the log.

Senior scenario: you need to delete 200 million old rows from a busy 24/7 orders table. How do you do it? Not in one statement: that would grow the log enormously, hold locks for hours and block the application. If the table is partitioned by date, switch the old partitions out to a staging table and truncate it, which is a metadata operation. Otherwise delete in small batches (for example 5,000 rows) using an index on the date column, with a short pause between batches, log backups running so the log can be reused, and progress tracking so the job can be stopped and resumed. Check foreign keys and triggers first, and consider archiving with OUTPUT deleted.* INTO in the same statement.