Using the INSERT ... SELECT Statement to Insert Query Results into a Table
Sometimes, instead of
the list of values, you may wish to insert results of a subquery into
your existing table. In this case, you will not use the VALUES clause. Instead of the VALUES clause you will use the INSERT statement with the SELECT clause. Here are the core elements of the INSERT...SELECT statement.
INSERT [INTO] table_or_view [(comma separated list of column names)]
derived_table
The derived_table is a result of a SELECT statement that returns a list of rows. The list of rows returned must have the same structure as the columns specified in the INSERT
statement. There must be the same number of columns returned, and they
must have the same data types. The following example demonstrates the
use of the INSERT statement with a SELECT subquery.
In Example 3, we will create a Colors table to store color values. We will then use a SELECT
statement to select all individual colors from the Production.Product
table. We will restrict the result set for non-null values and sort them
in alphabetical order. The entire SELECT statement will evaluate first, and the resultant set of rows will be inserted into the Colors table. If the SELECT statement returned more than one column, or the column returned by the query was of a different data type, the INSERT statement will fail.
Example 3. Inserting Rows Using a SELECT Subquery
CREATE TABLE dbo.Colors
(ColorID int IDENTITY NOT NULL PRIMARY KEY,
Color nvarchar(15))
GO
INSERT Colors
SELECT DISTINCT Color
FROM
Production.Product
WHERE Color is not NULL
ORDER BY Color
GO
SELECT * FROM Colors
|
Using the INSERT Statement with the EXECUTE Clause
The EXECUTE
keyword allows you to execute a stored procedure, or to interpret a
string as a Transact-SQL command, and execute the resultant command. If
you wish to insert results of a stored procedure into a table, you must
use the EXECUTE clause. Two correct uses of the INSERT...EXECUTE statement are shown in Examples 4 and 5. Both examples use the Colors table we created in an earlier example.
Example 4. INSERT Statement with the EXECUTE Clause (Stored Procedure)
CREATE PROC GetMostPopularColors
AS
SELECT Color
FROM Production.Product
JOIN Sales.SalesOrderDetail
ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
WHERE Color is not null
GROUP BY Color Having COUNT(*) > 10000
GO
INSERT Colors (Color)
EXECUTE GetMostPopularColors
GO
|
Example 5. INSERT Statement with the EXECUTE Clause (Inline Command)
INSERT Colors (Color)
EXECUTE ('SELECT "Orange" as Color')
GO
|
In Example 4, we have created a stored procedure named GetMostPopularColors.
This procedure returns colors of products that have been ordered more
than 10,000 times. We will then use the results to populate the Colors
table using INSERT... EXECUTE. In Example 7.5 we have created an inline string statement that simply selects the color Orange in the color column. Use the INSERT...EXECUTE (inline_statement) to treat the text as a command and execute it, and then insert the execution results into the Colors table.
Tip
Remember to use parentheses (round brackets) with the EXECUTE statement around the inline command text. If you forget the parentheses, you will get an error similar to this: Msg 102, Level 15, State 1, Line 2. Incorrect syntax near ‘SELECT ‘Orange’ as Color’.
Using the INSERT Statement with the TOP Expression
Sometimes you may wish to restrict the number of rows inserted into a table using the INSERT statement. Usually you would use the TOP (n) or the TOP (n) PERCENT clause to achieve this. The TOP (n) clause specifies that only the first n number of rows returned from the query result should be inserted using the INSERT statement. TOP (n) PERCENT is similar, except instead of specifying a numeric value you specify a percentage of the rows to be inserted. Often the ORDER BY clause is included in the SELECT query, so the rows are sorted before the top rows are identified.
Tip
You can use the TOP expression in exactly the same way with all DML statements: INSERT, UPDATE, DELETE, and MERGE. You can also use it with the SELECT statement. However, you can only use the WITH TIES expression with the SELECT statement.
The syntax for using the TOP expression with the INSERT statement is shown in Example 6.
Example 6. INSERT TOP...SELECT Statement—Core Elements
INSERT table_name
TOP (n) [PERCENT]
[WITH TIES]
SELECT ... FROM ... [JOIN...][WHERE...]
|
Here, n
is the numeric value that specifies the number of rows to be returned.
You must surround this value in parentheses when using the TOP expression with the INSERT statement. For backward compatibility, you can still use the TOP expression without parentheses, but in SELECT statements only. It is not recommended to call the TOP expression without the parentheses. If you specify an ORDER BY clause for your query, the top n rows are returned in that order. If your query does not use ORDER BY, the order of the rows is arbitrary.
If you specify the optional PERCENT keyword, the numeric value n will be treated as a percentage.
The WITH TIES keyword can be used only in SELECT statements. When specified, if there are rows that have the same values as the rows included in the top n set, they will be included also. Consequently, your result set may contain more rows than the actual n specified.
In Example 7
we will create a table called SickLeave containing EmployeeID,
FullName, and SickLeaveHours columns. We will insert the information
about the top 10 percent of employees with the most sick leave hours
into this table. We will also insert 20 employees who have taken the
least number of sick leave hours into this table (see Example 8).
Example 7. Using the INSERT TOP (n) PERCENT Statement
CREATE TABLE dbo.SickLeave
(EmployeeID int, FullName nvarchar(100), SickLeaveHours int)
GO
INSERT TOP (10)PERCENT
SickLeave
SELECT
EmployeeID, FirstName + ' ' + LastName, SickLeaveHours
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
ORDER BY SickLeaveHours DESC
-- Comment: 29 rows affected
|
Example 8. Using the INSERT TOP (n) Statement
INSERT TOP (20)
SickLeave
SELECT
EmployeeID, FirstName + ' ' + LastName, SickLeaveHours
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
ORDER BY SickLeaveHours ASC
-- Comment: 20 rows affected
|
|
Sometimes
we are asked to produce a random sample of rows from a particular
table, and insert them into another table. One way to do this is to use
the TOP (n) [PERCENT] expression, along with the NEWID () function. The NEWID () function produces a globally unique identifier (GUID) that is so close to random it can be treated as such. Sorting by the NEWID () function generates a GUID for each row. You can then use the TOP clause to take a certain number or percentage of rows.
In Example 9
we will create a table named SurveySample, which has a FullName column.
We will then use the technique described here to insert 10 random
employee names into the SurveySample table.
This method should not be
used with large tables (millions of rows), as the results will take a
very long time to return, and the query will consume a large amount of
memory. In these cases you should use an expression like ABS(CAST((BINARY_CHECKSUM(key_column, NEWID())) as int)) % 100) < n in the WHERE
clause. This expression generates a random number between 0 and 99 for
each row, then selects only those rows whose random number is under a
specified percentage.
|
Example 9. Generating a Random Sample of 10 Employees
CREATE TABLE SurveySample (FullName nvarchar(200))
GO
INSERT TOP(10)
SurveySample
SELECT
FirstName + ' ' + LastName
FROM Person.Contact JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactIDORDER BY NEWID()
|
