Share via

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

  • Article

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

Note

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.

Options

  • 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.

    Note

    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.

    Important

    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.

  • 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.

Change History

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.