DATABASE

SQL Server 2008 : Working with DML Queries - Using the INSERT Statement (part 2)

1/15/2011 9:07:57 AM

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

Configuring & Implementing...: Generating a Random Sample

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()


Other  
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 7) - Nested Tables
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 6) - Validating and Comparing Mining Models
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 5) - Viewing Mining Models
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 4) - Deploying and Processing Data Mining Objects
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 3) - Editing and Adding Mining Models
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 2) - Creating a Mining Model
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 1) - Creating a Mining Structure
  •  Microsoft SQL Server 2005 : Report Management
  •  Microsoft SQL Server 2005 : Report Definition and Design (part 5) - Report Builder
  •  Defensive Database Programming with SQL Server : Using TRY...CATCH blocks to Handle Errors
  •  
    Video
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone