Column Mappings (SQL Server Import and Export Wizard)

 

Updated: August 22, 2016

Applies To: SQL Server 2016

After you select the existing tables and views to copy or review the query that you provided, if you click Edit mappings, the SQL Server Import and Export Wizard shows the Column Mappings dialog box. On this page you specify and configure destination columns to receive the copied data.

The following screen shot shows the Column Mappings dialog box of the wizard.

In this example, you see that the wizard is going to create a new destination table, because Create destination table is selected. By default, each column in the new destination table has the same data type and properties as the corresponding source column.

Column mappings page of the Import and Export Wizard

Options

Source
Identifies the selected source table, view, or query.

Destination
Identifies the selected destination table, view, or query.

Create destination table/file
Specify whether to create the destination table if it does not already exist.

Edit SQL
Click Edit SQL to open the Create Table SQL Statement dialog box. Use the default CREATE TABLE statement or modify it for your purposes. If you change this statement manually, you have to make sure that the list of column mappings recognizes your changes. For more info, see Create Table SQL Statement.

System_CAPS_ICON_tip.jpg Tip

If you specified a new destination table on the Select Source Tables and Views page, the Create destination table option is automatically selected and the Edit SQL button is enabled. Otherwise, if you want to create the destination table, you have to go back to the Select Source Tables and Views page and enter the name of a new table in the Destination column.

If the Create destination table option and the Edit SQL button are disabled on the Column Mappings page, go back to the Select Source Tables and Views page and enter the name of a new table in the Destination column. After you specify a new destination table or tables and click Next, the Create destination table option is automatically selected and the Edit SQL button is enabled on the Column Mappings page. Select Edit SQL to show the Create Table SQL Statement dialog box.

Delete rows in destination table/file
Specify whether to clear the data from an existing table before loading the new data.

Append rows to destination table/file
Specify whether to append the new data to the data already present in an existing table.

Drop and re-create destination table
Choose this option to overwrite the destination table. This option is only available when you use the wizard to create the destination table. The destination table is only dropped and re-created if you save the package that the wizard creates, and then run the package again.

Enable identity insert
Choose this option to allow existing identity values in the source data to be inserted into an identity column in the destination table. By default, the destination identity column typically does not allow this.

System_CAPS_ICON_tip.jpg Tip

If your existing primary keys are in an identity column, an autonumber column, or the equivalent, you typically have to select this option to keep your existing primary key values. Otherwise the destination identity column typically assigns new values.

Mappings
Displays how each column in the data source maps to a column in the destination.

You don't have to copy all the columns in a table. Select ignore in the Destination column for columns that you don't want to copy.

Column mappings - mappings

The Mappings list has the following columns.

  • Source
    View each source column for which you can specify conversion settings if required.

  • Destination
    Specify whether you want to ignore a column during the copy operation. You can copy only a subset of columns by selecting ignore in this column for columns that you want to skip. Before you map columns, you must ignore all columns that will not be mapped.

  • Type
    Select a data type for the destination column. For info about how the wizard maps data types by default between source columns and destination columns, see How the Wizard maps data types from source to destination.

  • Nullable
    Specify whether the destination column allows a null value.

  • Size
    Specify the number of characters in the destination column.

  • Precision
    Specify the precision of numeric data in the destination column, referring to the number of digits.

  • Scale
    Specify the scale of numeric data in the destination column, referring to the number of decimal places.

After you specify and configure destination columns to receive the copied data and click OK, the Column Mappings dialog box returns you to the Select Source Tables and Views page or to the Configure Flat File Destination page. For more info, see Select Source Tables and Views or Configure Flat File Destination.

If you specified a mapping that may not succeed in the Mappings list, then the Column Mappings dialog box takes you to the Review Data Type Mapping page. On this page, you review the warnings, specify conversion options, and also specify how to handle errors. For more info, see Review Data Type Mapping.

Community Additions

ADD
Show: