SqlBulkCopy start index?

Question

I have a table that i want to copy to another table using SqlBulkCopy, but in my destination table i have an ID. So like this:

Source table

variable1, variable2, variable3, variable4

Destination table

ID, variable1, variable2, variable3, variable4

How can i make make sure that sqlbulkcopy starts at index 1 in my destination table? The only work around i've found is that adding ID as the last variable, like this:

Destination table(2)

variable1, variable2, variable3, variable4, ID

EDIT As seen in my answer to Christos, ID is already set as IDENTITY(1,1).

So what's wrong? You get an error? Your ID values are not correct? — TomT, Nov 10 '14 at 21:56
Ye, it says that my attributes doesn't match. Since it starts with the ID in my destination table. — Zee, Nov 11 '14 at 07:46

score 0 · Answer 1 · answered Nov 11 '14 at 09:07

If the schemas in source and destination are different you need to provide column mappings. If mappings are not defined then columns are mapped implicitly based on ordinal position. You can add the mappings like this:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
  bulkCopy.DestinationTableName = "destination_table";

  bulkCopy.ColumnMappings.Add("variable1", "variable1");
  bulkCopy.ColumnMappings.Add("variable2", "variable2");
  bulkCopy.ColumnMappings.Add("variable3", "variable3");

  // etc.

This way the ID column in destination will be ignored and the default value (identity) will be used to populate it.

SqlBulkCopy start index?

1 Answers1