Inserting Rows by Using INSERT and SELECT Subqueries
The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at the same time.
In the following example the INSERT statement inserts into a separate table some of the data from all the rows in the Sales.SalesReason table in AdventureWorks whose SalesReason is Marketing.
USE AdventureWorks;
GO
CREATE TABLE MySalesReason (
SalesReasonID int NOT NULL,
Name nvarchar(50),
ModifiedDate datetime);
GO
INSERT INTO MySalesReason
SELECT SalesReasonID, Name, ModifiedDate
FROM AdventureWorks.Sales.SalesReason
WHERE ReasonType = N'Marketing';
GO
SELECT SalesReasonID, Name, ModifiedDate
FROM MySalesReason;
GO
The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into.
Another use of the INSERT...SELECT statement is to insert data from a source outside SQL Server 2005. The SELECT in the INSERT statement can be used for the following:
- Reference a remote table on a linked server by using a four-part name. For more information, Identifying a Data Source by Using a Linked Server Name.
- Reference a remote table using OPENROWSET. For more information, see Identifying a Data Source by Using an Ad Hoc Name.
- Use the result set of a query executed on a remote server. For more information, see Using Pass-Through Queries As Tables.
See Also
Concepts
Adding Rows by Using INSERT and SELECT
Inserting a Row by Using INSERT and Values
Limiting Inserted Rows by Using TOP
Inserting Rows by Using SELECT INTO