Bryce,
SqlBulkCopy will, exclude the identity column when inserting new rows
into the destination table. I've included a small C# sample to
demonstrate. The sample creates a small table with two columns and adds
three rows. The code retrieves the three rows into a DataTable and then
modifies the non-key column (so you can tell the originals and copies
apart) and the key column before adding these rows to the table via
SqlBulkCopy. Using the default SqlBulkCopyOptions, you'll see that the
identity values from the DataTable are ignored and that the database
generates new values for the identity column. If you change the code to
use SqlBulkCopyOptions.KeepIdentity, you would see the identity values from
the DataTable used.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.
string strConn, strSQL;
strConn = @"Data Source=.\SQLExpress;" +
"Initial Catalog=Northwind;Integrated Security=True;";
strSQL = "SELECT ID, OtherColumn FROM SqlBulkCopyTest";
using (SqlConnection cn = new SqlConnection(strConn))
{
cn.Open();
PrepDb(cn);
DataTable tbl = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
da.Fill(tbl);
foreach (DataRow row in tbl.Rows)
{
row["OtherColumn"] = ((string)row["OtherColumn"]).
Replace("Original", "Copied");
row["ID"] = ((int)row["ID"]) + 10;
}
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default;
SqlBulkCopy bcp = new SqlBulkCopy(cn, options, null);
bcp.DestinationTableName = "SqlBulkCopyTest";
bcp.WriteToServer(tbl);
tbl.Clear();
da.Fill(tbl);
foreach (DataRow row in tbl.Rows)
Console.WriteLine("{0}: {1}", row["ID"], row["OtherColumn"]);
}
static void PrepDb(SqlConnection cn)
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DROP TABLE SqlBulkCopyTest";
try { cmd.ExecuteNonQuery(); }
catch { }
cmd.CommandText = "CREATE TABLE SqlBulkCopyTest " +
"(ID int identity PRIMARY KEY, " +
"OtherColumn nvarchar(255))";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SqlBulkCopyTest " +
"(OtherColumn) VALUES (@OtherColumn)";
SqlParameter p = cmd.Parameters.Add("@OtherColumn",
SqlDbType.NVarChar, 255);
p.Value = "First Original Row";
cmd.ExecuteNonQuery();
p.Value = "Second Original Row";
cmd.ExecuteNonQuery();
p.Value = "Third Original Row";
cmd.ExecuteNonQuery();
}
