Using SqlBulkCopy, how do I insert data into a table in a non-default database schema?

Question

I need to insert data into a table in a schema named Staging using SqlBulkCopy.

It appears the API only allows you to set the target table name by using the DestinationTableName property.

How do I accomplish this? Is it possible?

Answer 1

While the API does not provide an explicit interface to set the destination schema, you can actually stuff a 2- or 3-part qualified table name into the DestinationTableName property like so:

b.DestinationTableName = string.Format("[{0}].[{1}]", schemaName, tableName);

or

b.DestinationTableName =
    string.Format("[{0}].[{1}].[{2}]", databaseName, schemaName, tableName);

Given that BULK INSERT has always supported a fully-qualified table name, it seems like a big oversight that those separate components never made it into the API.

Moreover, as it appears that DestinationTableName is simply output into a BULK INSERT statement, this property may be vulnerable to SQL injection. So if you're getting this information from a user at some point, make sure to sanitize the input before running this operation.