Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 39 ยท Data Modification and Transactions

INSERT, UPDATE and DELETE in T-SQL

Change data in SQL Server with INSERT, UPDATE, DELETE, TOP, OUTPUT and SELECT INTO. Learn the T-SQL extras, batch deletes and safe habits, with runnable stand-ins.

Reading data is only half of the job. Sooner or later you have to add rows, fix them and remove them. SQL Server follows the standard INSERT, UPDATE and DELETE statements you may know from the SQL tutorial, and then adds several T-SQL extras: multi-row VALUES, TOP on writes, UPDATE ... FROM joins, the OUTPUT clause and SELECT ... INTO.

SQL Server itself cannot run in your browser, so the T-SQL-only statements below are shown as read-only code, each followed by a runnable stand-in written in portable SQL. The stand-in teaches the same idea on the editor's SQLite engine.

INSERT

Always list the columns. An insert that relies on the table's column order breaks the day someone adds a column.

SQL
INSERT INTO dbo.Products (Name, Category, Price)
VALUES (N'Desk lamp', N'Home', 24.90),
       (N'Notebook',  N'Office', 3.50),
       (N'Stapler',   N'Office', 7.25);

One VALUES list can hold up to 1,000 rows. The N prefix marks a Unicode string literal for nvarchar columns. Without it, characters outside the database's code page can turn into question marks.

Copy rows from a query with INSERT ... SELECT:

SQL
INSERT INTO dbo.ProductArchive (ProductId, Name, Price)
SELECT ProductId, Name, Price
FROM dbo.Products
WHERE Discontinued = 1;

Identity columns are filled in automatically. Read the new key with SCOPE_IDENTITY() (the last identity made in your current scope) rather than @@IDENTITY, which can pick up values created by triggers.

SQL INSERT with several rows and INSERT ... SELECT

Press Run (or Ctrl + Enter) to see the result here.

UPDATE and UPDATE ... FROM

A basic update changes rows that match WHERE:

SQL
UPDATE dbo.Products
SET Price = Price * 1.10
WHERE Category = N'Office';

Forgetting WHERE updates every row. Before you run an update or delete against real data, turn it into a SELECT first and check the rows it would touch.

T-SQL can also update from a join. You name the table to change in UPDATE, then bring in another table in FROM:

SQL
UPDATE p
SET    p.Price = p.Price * (1 - d.Percent / 100.0)
FROM   dbo.Products AS p
JOIN   dbo.Discounts AS d ON d.Category = p.Category;

If a product matches more than one discount row, SQL Server doesn't complain. It silently applies one of them, and which one is not defined. Make sure the join yields one row per target row.

SQL Update from another table

Press Run (or Ctrl + Enter) to see the result here.

DELETE and TRUNCATE

SQL
DELETE FROM dbo.Orders
WHERE Status = N'cancelled' AND OrderDate < DATEADD(YEAR, -2, SYSDATETIME());

DELETE removes rows one by one, fires triggers, and is fully logged. TRUNCATE TABLE dbo.Orders deallocates the pages instead: it is much faster, resets the identity counter and fires no delete triggers, but it cannot be used on a table that a foreign key references, and it has no WHERE.

TOP on writes and batch deletes

Huge deletes hold locks and grow the transaction log. The usual cure is to delete in chunks:

SQL
WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM dbo.AuditLog
    WHERE CreatedAt < '2024-01-01';

    IF @@ROWCOUNT = 0 BREAK;
END;

Each loop is its own small transaction, so locks are released between chunks and the log can be reused. TOP in a write has no ORDER BY; if the choice of rows matters, delete through a CTE that selects TOP (n) ... ORDER BY.

The OUTPUT clause

OUTPUT returns the rows affected by INSERT, UPDATE, DELETE or MERGE, using two pseudo-tables: inserted (the new values) and deleted (the old ones).

SQL
UPDATE dbo.Products
SET    Price = Price * 1.10
OUTPUT deleted.ProductId, deleted.Price AS OldPrice, inserted.Price AS NewPrice
WHERE  Category = N'Office';

That's a one-statement audit trail, and it is also the safe way to get back generated identity values for a multi-row insert. You can send the output to a table with OUTPUT ... INTO @Changes.

SQLite has RETURNING, which gives the same effect for new values:

SQL RETURNING as a stand-in for OUTPUT

Press Run (or Ctrl + Enter) to see the result here.

SELECT ... INTO

SELECT ... INTO creates a new table from a query result in one step:

SQL
SELECT ProductId, Name, Price
INTO   dbo.ProductsBackup
FROM   dbo.Products;

Handy for quick backups and temp tables (INTO #work). The new table gets column names and types from the query, but no indexes, keys or constraints. The portable form is CREATE TABLE ... AS SELECT, which SQLite also supports.

flowchart LR
  A["INSERT / UPDATE / DELETE"] --> B["Rows change in the table"]
  A --> C["OUTPUT clause"]
  C --> D["inserted: new values<br/>deleted: old values"]
  D --> E["Client or audit table"]

OUTPUT sees the same before and after images that triggers see.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the OUTPUT clause do?

    OUTPUT exposes the inserted and deleted pseudo-tables for the rows a statement changed.
  2. 2. Which function is safest for reading the identity value you just inserted?

    SCOPE_IDENTITY() is limited to your scope, so identities generated by triggers do not leak into it. MAX(Id) can race with other sessions.
  3. 3. Why delete a large table in batches of a few thousand rows?

    One huge transaction holds locks for a long time and forces the log to keep growing. Small chunks commit often.
  4. 4. What is true about TRUNCATE TABLE compared with DELETE?

    TRUNCATE is minimally logged, resets the identity seed and removes every row; a referenced table cannot be truncated.

Summary

  • List columns in every INSERT; use N'...' for Unicode literals; read new keys with SCOPE_IDENTITY().
  • UPDATE ... FROM ... JOIN is a T-SQL extension; make sure the join does not produce duplicates.
  • Write a SELECT first, then turn it into an UPDATE or DELETE.
  • Delete big sets in TOP (n) batches; TRUNCATE is the fast reset.
  • OUTPUT returns old and new row values; SELECT ... INTO copies data but not indexes.

Next: MERGE in SQL Server