The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P70: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | ETL Techniques Chapter 8 327 When the preceding command is run it produces a data file named that looks like this 1. George Washington 2. John Adams 3. Thomas Jefferson You could turn around and import the data right back into the same table with the command again the command should be entered on a single line bcp in -T -f Of course the problem would be that the data file has PresidentIDs in it that conflict with the PresidentIDs of the same records already in the table. What would happen Well SQL Server would just ignore the identity values in the data file and generate new identity values. Three new rows would be added with the same names as before but with new PresidentIDs. If you wanted to override the identity behavior of the PresidentID field you could use the -E option of BCP to keep the identity values in the data file. In this case the load would fail because that column also has a primary key constraint on it and as the rows were inserted with the same PresidentID values as the existing rows the primary key violation would keep the import from succeeding. What if the source file didn t have any values for the PresidentIDs If you were to edit the file that was produced from your earlier output you could manually remove the ID values to make the file look like this George Washington John Adams Thomas Jefferson To make this work you would then need to edit the Format file as well. The format file would need to indicate that there are now only two fields in the data file rather than three and that the data file fields map to the FirstName and LastName fields in the table. Your edited file would look like this 2 1 SQLCHAR 0 50 t 2 FirstName SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 50 r n 3 LastName SQL_Latin1_General_CP1_CI_AS Notice in the preceding format file that the first field in the data file is mapped to .
