SQL Server - INSERT INTO table with value SELECT FROM another table

You can use SELECT FROM statement to retrieve data from this table, then use an INSERT INTO to add that set of data into another table, and two statements will be nested in one single query.

1. Insert an entire column’s data

The general syntax would be:

INSERT INTO table_a (col1a)
SELECT col1b
FROM table_b;

That statement will select all data from col1b in table_b and insert into col1a in table_a.

You can insert multiple columns from multiple columns:

INSERT INTO table_a (col1a, col2a, col3a, …)
SELECT col1b, col2b, col3b, …
FROM table_b;

2. Insert some rows from another table.

You can add some conditions before inserting to limit the results:

INSERT INTO table_a (col1a, col2a, col3a, …)
SELECT col1b, col2b, col3b, …
FROM table_b
WHERE table_b.col1 = x;

Example: INSERT data of big orders from the table of total orders, where the total amount of money is larger than $10,000:

INSERT INTO sales.big_orders (id, full_name, address, total)
SELECT
	id,
	full_name,
	address,
	total
FROM
	sales.total_orders
WHERE
	total > 10000;

3. Insert top rows

You can choose top n rows from the table_b to insert into table_a by this query:

INSERT TOP(n)
INTO table_a (col1a, col2a, col3a, …)
SELECT col1b, col2b, col3b, …
FROM table_b
WHERE table_b.col1 = x
ORDER BY col1b;

With the example above, we want to insert only the top 20 biggest orders:

INSERT INTO sales.big_orders (id, full_name, address, total) 
SELECT
    id,
    full_name,
    address,
    total
FROM
    sales.total_orders
WHERE
    total > 10000
ORDER BY total;

4. Insert both from columns and defined values.

In case you insert data into multiple columns, but only some columns need to import from the table_b, some columns need to import from another set of data:

INSERT INTO table_a (col1a, col2a, col3a, col4a …)
SELECT table_b.col1b, 'other value', table_b.col3b, 'another_value',…
FROM table_b
WHERE table_b.col1 = x;

---

Need a good GUI Client to work with MS SQL Server? TablePlus provides a modern, native tool with intuitive UI to manage multiple databases in cluding SQL Server, MySQL, PostgreSQL, SQLite, Oracle…

And it’s available for free!

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS