Export (0) Print
Expand All

Select Source Tables and Views (SQL Server Import and Export Wizard)

Updated: 17 July 2006

Use the Select Source Tables and Views page to specify the tables and views to be copied from the data source to the destination.

ms190148.note(en-US,SQL.90).gifNote:
You do not have to copy all the columns in a table when you select the Table Copy option. After selecting a destination table, click Edit Mappings to display the Column Mappings dialog box. Select <ignore> in the Destination column of the Column Mappings dialog box for columns that you want to skip.

To learn more about this wizard, see Creating Packages Using the SQL Server Import and Export Wizard. To learn about the options for starting the wizard, and about the permissions required to run the wizard successfully, see How to: Start the SQL Server Import and Export Wizard.

The purpose of the SQL Server Import and Export Wizard is to copy data from a source to a destination. The wizard can also create a destination database and destination tables for you. However, if you have to copy multiple databases or tables, or other kinds of database objects, you should use the Copy Database Wizard instead. For more information, see Using the Copy Database Wizard.

Source

Using the check boxes, select from the list of available tables and views to copy to the destination. If you select a source table or view and perform no other action, the schema and data from the source are copied without changes.

Destination

Select a destination table from the list for each source table.

ms190148.note(en-US,SQL.90).gifNote:
If you pause at this point in the wizard to create a destination table in SQL Server by using Management Studio or another tool, the new table is not immediately visible in the list of available destination tables. To refresh the list of destination tables, step back two pages to the Choose a Destination page, re-select the destination database, then step forward again to the Select Source Tables and Views.

Optimize for many tables

Specify whether to use a processing method that is more scalable for a large number of tables and views.

ms190148.note(en-US,SQL.90).gifImportant:
When you select the Optimize for many tables option, the wizard ignores the Enable identity insert setting in the Column Mappings dialog box and renumbers identity values at the destination. This can cause unexpected results when related tables use the identity values as foreign keys.

When you select more than 20 tables or views, this option is automatically selected for you. When you select more than 100 tables or views, and you uncheck this option, a warning message is displayed.

When this option is not selected, the import or export operation uses a single Integration Services package with a single Data Flow task that includes one parallel path, from source to destination, for each table or view to be copied. This simple implementation is intended for a relatively small number of tables.

When this option is selected, the import or export operation creates a temporary package dynamically for each table or view to be copied. This implementation scales effectively for more than 20 tables and views.

You can examine the effects of this option by:

  • Observing the series of actions performed during the import or export operation on the Performing Operation page of the wizard.
  • Saving packages created with different settings for this option.

If you intend to save the package created by the wizard and customize the import or export of data by adding transformations, you must leave this option unselected.

Run in a transaction

Specify whether to perform the import or export within a transaction. For more information on this option, see Incorporating Transactions in Packages.

Edit mappings

Use the Column Mappings dialog box to specify destination columns to receive the source data. You can copy only a subset of columns by selecting <ignore> in the Destination column of the Column Mappings dialog box for columns that you want to skip.

Preview

Preview source data in Data View to verify it before performing the import or export. Data View displays up to 200 rows of data. To make corrections, click the Back button in the Select Source Tables and Views dialog box to return to previous dialog boxes.

Release History

15 September 2007

New content:
  • Described how the Optimize for many tables option results in the identity values being renumbered at the destination.

17 July 2006

New content:
  • Provided information about how to skip columns.
  • Described behavior when a new table is created by using the wizard.

5 December 2005

New content:
  • Provided details about optimization.

Community Additions

ADD
Show:
© 2014 Microsoft