The DTS Import and DTS Export wizards allow you to map a source column to a destination column as the data is copied.
You can stipulate how a source is mapped to a destination by:
For example, you could copy the ord_date column of data type smalldatetime to a destination column new_ord_date with a data type of datetime. Alternatively, all the columns in the sales table except the payterms column could be copied to a new table.
If you create a new table with more columns than exist at the source, you cannot map to all of the destination table columns in the wizard. You are limited to the number of columns that exist in the source table. Use DTS Designer for this operation.
You can determine what happens to existing data, if any, in the destination table before the source data is copied.
| Use this | To do this |
|---|---|
| Create destination table | Create the destination table before copying source data (default if the table does not exist). If the destination table exists, an error may occur, unless Drop and recreate destination table if it exists is selected. |
| Delete rows in destination table | Delete all rows in the destination table before copying source data. This option is only available if the destination table already exists. Indexes and constraints on the destination table remain. |
| Append rows to destination table | Insert source data into the destination table (default). All existing data, indexes, and constraints on the destination table remain. Rows are not necessarily appended to the end of the destination table. Only by using a clustered index on the destination table can you determine where rows are inserted. This option is only available if the destination table already exists. |
| Edit SQL | Modify the default CREATE TABLE statement generated by the DTS wizard. |
| Drop and recreate destination table | Drop the destination table if it exists and re-create it before attempting to copy data into it. All existing data in the destination table and any indexes are permanently deleted. |
| Enable identity insert | Allow explicit values to be inserted into the identity column of a table (SQL Server only). This option is only available if an identity column is detected. |
For each column of source data under Mappings, these properties must be specified.
| Use this | To do this |
|---|---|
| Source | Choose the column name in the source table to copy to Destination. Choose <ignore> if the source data should not be copied. If Destination is defined as NOT NULL, then entering <ignore> can result in an error if there is no DEFAULT value for Destination. |
| Destination | Enter the column name in the destination table where the data from Source should be inserted. If the destination table is being created, a new column name can be entered. Enter <ignore> if the destination column should not be created. If <ignore> is chosen and the destination column already exists, Source is not copied for this column. |
| Type | Enter the data type for each Destination column. Only a valid data conversion should be specified. For example, while it is possible to convert an int to a tinyint, without potential data truncation, the operation will fail if the conversion would result in data truncation. |
| Nullable | Select if Destination can allow null values. |
| Size | Enter the length in units corresponding to the data type of the Destination column. This value is only applicable for the char, varchar, nchar, nvarchar, binary, and varbinary data types. A size smaller than the length of Source can result in data truncation. |
| Precision | Enter the maximum number of decimal digits that can be stored, to the left and to the right of the decimal point. Applies to decimal and numeric data types only. |
| Scale | Enter the maximum number of decimal digits that can be stored to the right of the decimal point. Must be less than or equal to Precision. Applies to decimal and numeric data types only. |
The default mapping specifies that: