Inserting Data from Another Table

The previous lesson covered SQL Server's punctuation rules for literal values: which datatypes need quotes, and how to escape an apostrophe inside a string. This lesson moves from values you type yourself to values that already live somewhere else in your database. Instead of writing out a VALUES list by hand, you will construct INSERT statements that pull their data directly from another table's rows.

Copying rows from one table into another is one of the most common tasks in day-to-day database work. A few typical scenarios:

• Archiving: moving completed orders out of an active table and into a history table.
• Auditing: recording a "before" snapshot of a row just before it changes, so you can see what it used to look like.
• Staging and reporting: populating a summary or reporting table from detailed transactional data.
• Migration: copying selected rows from one system's table into another's during a data migration.

In each case, the source data already exists; you just need to get it into a new location, sometimes transforming it slightly along the way.

The core tool for this job is INSERT INTO ... SELECT:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE search_condition;

A few rules govern how this works:

• The SELECT list must line up with the INSERT column list, in the same order, with matching (or implicitly convertible) data types.
• The WHERE clause filters the source table, not the target: it decides which source rows get copied.
• You can pull static or calculated values into the mix alongside columns, as you will see below.
• target_table must already exist; if it doesn't, see SELECT ... INTO further down.

Suppose Widget Sales Service (WSS) wants to track salary history for its employees. Every time an employee receives a raise, WSS needs to record that employee's salary immediately before the raise, along with the date the raise took effect. WSS already has a SalaryHistory table for this purpose:

The values WSS needs to insert, last name, first name, and current (soon-to-be-prior) salary, already live in the company's Employee table:

Mickey Mouse has earned a raise, from $20,000 to $25,000. Before WSS applies that raise in the Employee table, it needs a SalaryHistory record capturing his prior salary and the date of the change. Both the salary and the row-selection values come straight from Employee, so a single INSERT ... SELECT statement handles it:

INSERT INTO SalaryHistory (LastName, FirstName, PriorSalary, RaiseDate)
SELECT LastName, FirstName, CurrentSalary, GETDATE()
FROM Employee
WHERE LastName = N'Mouse'
  AND FirstName = N'Mickey';

Notice that RaiseDate isn't pulled from Employee at all, since Employee has no such column; it's generated on the spot with GETDATE(). This is a good illustration of the point above: a SELECT list can freely mix columns copied from the source table with literal or calculated values, as long as the total column count and order still match the INSERT list. After this statement runs, SalaryHistory looks like this:

With the history recorded, a separate UPDATE statement (covered in an earlier lesson) would then raise Mickey's actual salary in the Employee table. If you want a higher-precision timestamp than GETDATE() provides, SYSDATETIME() returns a datetime2 value accurate to 100 nanoseconds; either works here, and both remain fully current in SQL Server 2025.

The column-matching rule above has one common wrinkle: IDENTITY columns. If your target table has an IDENTITY column (an auto-incrementing primary key, for example), leave it out of both the INSERT column list and the SELECT list. SQL Server generates the next value automatically:

INSERT INTO SalaryHistory (LastName, FirstName, PriorSalary, RaiseDate)
SELECT LastName, FirstName, CurrentSalary, GETDATE()
FROM Employee
WHERE LastName = N'Mouse' AND FirstName = N'Mickey';
-- SalaryHistoryID (an IDENTITY column) is generated automatically, not supplied here

Occasionally you need to preserve specific ID values from the source table instead, for example when migrating historical records from an old system where those IDs are still referenced elsewhere. Suppose WSS is migrating rows from a legacy OldSalaryHistoryArchive table that already has its own SalaryHistoryID values assigned. SQL Server allows this, but only with IDENTITY_INSERT turned on explicitly for the duration of the statement:

SET IDENTITY_INSERT SalaryHistory ON;

INSERT INTO SalaryHistory (SalaryHistoryID, LastName, FirstName, PriorSalary, RaiseDate)
SELECT SalaryHistoryID, LastName, FirstName, PriorSalary, RaiseDate
FROM OldSalaryHistoryArchive;

SET IDENTITY_INSERT SalaryHistory OFF;

Only one table per session can have IDENTITY_INSERT on at a time, so always turn it back off once you're done.

The SELECT in an INSERT ... SELECT statement is a normal SELECT, which means it can join multiple source tables together, not just read from one. Suppose WSS also keeps a small Department lookup table translating the department codes on Employee into full names:

To build an EmployeeDirectory table that lists each employee alongside their full department name, rather than the bare code, join Employee to Department inside the SELECT:

INSERT INTO EmployeeDirectory (LastName, FirstName, DeptName)
SELECT e.LastName, e.FirstName, d.DeptName
FROM Employee AS e
JOIN Department AS d
  ON e.Dept = d.DeptCode;

Table aliases (e, d) keep the column references unambiguous once more than one table is in play. Everything from the syntax rules above still applies: the SELECT list, now assembled from two tables instead of one, still has to line up column-for-column with the INSERT list.

Everything so far assumes SalaryHistory already exists. If you instead needed to spin up a brand-new table, say, a one-off snapshot of every WSS employee earning over $25,000, SELECT ... INTO builds the table and populates it in a single step:

SELECT LastName, FirstName, Dept, CurrentSalary
INTO HighEarners
FROM Employee
WHERE CurrentSalary >= 25000;

HighEarners doesn't need to exist beforehand; SQL Server creates it with column names and data types inferred from the SELECT list. There's an important limitation to keep in mind, though: SELECT ... INTO copies column names, data types, and nullability, but it does not copy primary keys, foreign keys, indexes, triggers, or default constraints from the source table. Those have to be added separately with their own ALTER TABLE or CREATE INDEX statements if the new table needs them.

INSERT ... SELECT has no built-in awareness of what's already in the target table. Suppose WSS accidentally runs the statement for Mickey Mouse a second time:

INSERT INTO SalaryHistory (LastName, FirstName, PriorSalary, RaiseDate)
SELECT LastName, FirstName, CurrentSalary, GETDATE()
FROM Employee
WHERE LastName = N'Mouse' AND FirstName = N'Mickey';
-- Run a second time by mistake: SalaryHistory now has TWO Mickey Mouse rows

Nothing about the statement itself prevents this. It succeeds both times and quietly leaves a duplicate SalaryHistory row behind, which is exactly the kind of trap a multi-step "insert the salary, then insert the salary again with a date added" approach could fall into, so it is worth watching for whenever a table doesn't have a constraint stopping it.

For situations where you need to insert only the rows that are genuinely new, while updating the ones that already exist, SQL Server offers the MERGE statement, an "upsert" (update-or-insert) in a single pass:

MERGE INTO SalaryHistory AS Target
USING (SELECT LastName, FirstName, CurrentSalary, GETDATE() AS RaiseDate
       FROM Employee
       WHERE LastName = N'Mouse' AND FirstName = N'Mickey') AS Source
   ON Target.LastName = Source.LastName AND Target.FirstName = Source.FirstName
WHEN NOT MATCHED THEN
    INSERT (LastName, FirstName, PriorSalary, RaiseDate)
    VALUES (Source.LastName, Source.FirstName, Source.CurrentSalary, Source.RaiseDate);

Run this twice and the second run inserts nothing, since WHEN NOT MATCHED only fires when no matching row already exists. MERGE has its own full syntax and its own set of caveats, which this module covers in more depth separately; for now, it's worth knowing the tool exists so you reach for it deliberately rather than layering repeated INSERT ... SELECT statements and hoping duplicates don't happen.

Sometimes the data you want to insert isn't sitting in a plain table at all, it's the output of a stored procedure. INSERT INTO ... EXEC routes that procedure's result set directly into a table:

INSERT INTO MonthlySalesSummary (Region, TotalSales, OrderCount)
EXEC usp_GetMonthlyMetrics @Year = 2025, @Month = 10;

The procedure's result set must match the target column list the same way a SELECT list would. This is a convenient way to capture and store the output of reporting procedures, or dynamic SQL batches, without writing a separate query to recreate that same logic.

A couple more tools are worth knowing once you're comfortable with the basic statement:

• Checking how many rows were affected with @@ROWCOUNT. Right after any INSERT, SELECT @@ROWCOUNT; reports how many rows the statement just touched, a quick sanity check when you're not certain your WHERE clause matched the number of source rows you expected.
• Minimal logging with TABLOCK. When inserting a large volume of rows into an empty table (or a heap) under the SIMPLE or BULK_LOGGED recovery model, adding the WITH (TABLOCK) hint lets SQL Server use minimal logging, which can significantly speed up large transfers:

  INSERT INTO SalaryHistory WITH (TABLOCK) (LastName, FirstName, PriorSalary, RaiseDate)
  SELECT LastName, FirstName, CurrentSalary, GETDATE()
  FROM Employee
  WHERE CurrentSalary >= 25000;
  

• Capturing what was inserted with OUTPUT. Rather than running a second query to see what you just inserted, the OUTPUT clause reports the inserted rows on the spot, and can log them into a separate audit table at the same time:

  INSERT INTO SalaryHistory (LastName, FirstName, PriorSalary, RaiseDate)
  OUTPUT inserted.LastName, inserted.FirstName, inserted.RaiseDate INTO SalaryHistoryAudit
  SELECT LastName, FirstName, CurrentSalary, GETDATE()
  FROM Employee
  WHERE LastName = N'Mouse' AND FirstName = N'Mickey';
  

Neither of the last two is something you'll need on every INSERT, but both are standard tools for production-scale data movement.

Ready to practice? The exercise below gives you another scenario for inserting data pulled from one table into another, so you can work through the column-matching and WHERE-filtering rules on your own.
Inserting Data Values - Exercise In the next lesson, you will learn how to update data using values from another table.