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!
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS