Developers copy rows from one table to another or within a table using an Insert Results query. When they create an Insert Results query, they specify:
– The database table to copy rows to (the destination table).
– The table or tables to copy rows from (the source table). The source table or tables become part of a subquery. If they are copying within a table, the source table is the same as the destination table.
– The columns in the source table whose contents they want to copy.
– The target columns in the destination table to copy the data to.
– Search conditions to define the rows they want to copy.
– Sort order, if they want to copy the rows in a particular order.
– Group By options, if they want to copy only summary information.
For example, the following query copies title information from the clients table to an archive table called archiveclients. The query copies the contents of five columns for all clients belonging to a particular account officer:
INSERT INTO archiveclients
(client_id, title, fname, lname, account_officer_id)
SELECT client_id, title, fname, lname, account_officer_id
FROM clients
WHERE (account_officer_id = ‘AF7788KLO’)
When developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to create an Insert results queries, they should follow the next steps:
1. Create a new query and add the table from which they want to copy rows (the source table). If they are copying rows within a table, they can add the source table as a destination table.
2. From the Query Designer menu, point to Change Type, and then click Insert Results.
3. In the Choose Target Table for Insert Results Dialog Box, select the table to copy rows to (the destination table). The Query and View Designer cannot determine in advance which tables and views they can update. Therefore, the Table Name list in the Choose Table for Insert From Query dialog box shows all available tables and views in the data connection they are querying, even those that they might not be able to copy rows to.
4. In the rectangle representing the table or table-valued object, choose the names of the columns whose contents they want to copy. To copy entire rows, choose * (All Columns).
The Query and View Designer adds the columns they choose to the Column column of the Criteria pane.
5. In the Append column of the Criteria pane, select a target column in the destination table for each column they are copying. Choose tablename.* if they are copying entire rows. The columns in the destination table must have the same (or compatible) data types as the columns in the source table.
6. If they want to copy rows in a particular order, specify a sort order
7. Specify the rows to copy by entering search conditions in the Filter column.
If they do not specify a search condition, all rows from the source table will be copied to the destination table. When they add a column to search to the Criteria pane, the Query and View Designer also adds it to the list of columns to copy. If they want to use a column for searching but not copy it, clear the check box next to the column name in the rectangle representing the table or table-valued object.
8. If they want to copy summary information, specify Group By options.