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.
Topics on this page
Section titled “Topics on this page”| 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
Section titled “INSERT”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.AmountVALUES (1006, 3, '2026-11-10', 'Pending', 62.00), (1007, 3, '2026-11-12', 'Pending', 18.40);Expected result:
Id CustomerId Status Amount1006 3 Pending 62.001007 3 Pending 18.40Pitfalls:
- 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 … INTOversusINSERT … SELECT.SELECT … INTO NewTablecreates 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
Section titled “UPDATE”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 changedUPDATE oSET o.Status = 'OnHold'OUTPUT inserted.Id, deleted.Status AS OldStatus, inserted.Status AS NewStatusFROM dbo.Orders AS oJOIN dbo.Customers AS c ON c.Id = o.CustomerIdWHERE c.IsActive = 0 AND o.Status = 'Pending';Expected result (Chen Wei is inactive and owns the two orders added above):
Id OldStatus NewStatus1006 Pending OnHold1007 Pending OnHoldPitfalls:
- A forgotten or partial
WHERE. Run the sameFROM … WHEREas aSELECTfirst, check the row count, then run theUPDATEinside 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
Section titled “DELETE”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 ordersDELETE FROM dbo.OrdersOUTPUT deleted.Id, deleted.CustomerId, deleted.AmountWHERE Status = 'Cancelled';Expected result:
Id CustomerId Amount1004 2 300.00In 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:
DELETEwithoutWHERE. 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
IsDeletedflag or aDeletedAtdate instead, and filter it out in views.
Transactions
Section titled “Transactions”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 intASBEGIN 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 changeEXEC dbo.CancelOrder @OrderId = 1003; -- fails: nothing is changedExpected 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 ONand aCATCHblock, a failedINSERTfollowed by a successfulUPDATEcommits half the work. - Nested
BEGIN TRANSACTIONis not a real nested transaction. Inner commits only decrement@@TRANCOUNT; aROLLBACKanywhere rolls back everything. UseSAVE TRANSACTIONfor partial rollback. NOLOCKas a fix for blocking.READ UNCOMMITTEDreads uncommitted and even duplicated or missing rows. ConsiderREAD_COMMITTED_SNAPSHOTinstead, after testing tempdb version-store usage.
Choosing between them
Section titled “Choosing between them”| 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 |
Interview questions
Section titled “Interview questions”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.
Related pages
Section titled “Related pages”- Querying data: the sample data and
WHEREfilters reused here. - Functions and stored procedures: wrapping changes in procedures.
- Tables, constraints and indexes: the constraints that reject bad writes.
- Temporal tables: automatic history of every change.