- In Microsoft SQL Server 2005, you use SQL Server Management Studio to import data or to export data from a source table to a destination table.
- Both the source table and the destination table include an identity column.
- You enable the Enable identity insert option for the source table.
- In the SQL Server Import and Export Wizard, you enable the Optimize for many tables option.
Note This problem does not occur in Microsoft SQL Server 2000.
If you do not enable the Optimize for many tables option, the data operation may take a longer time to finish. If you want to preserve the identity values when you import or export a large amount of data, we recommend that you use one of the following methods:
- Use the bcp utility together with the –E switch to transfer the table.
For example, first run a command that resembles the following command to export data from the source table to a .txt file:Bcp.exe SourceTable out C:\FileName.txt -S. -T -c –EThen, run a command that resembles the following command to import data to the destination table:Bcp.exe DestinationTable in C:\FileName.txt -S. -T -c -E
- Detach the database, and then attach the database to the destination server.
Article ID: 952111 - Last Review: Jun 16, 2008 - Revision: 1