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

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?

Answers


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.


Need Your Help

How to remove the entire line of a word doc using vba

vba ms-word word-vba

The following line of code will insert text into a bookmark range in a word document.

Changing order of csv-file entries with regex replacement in Notepad++

regex csv notepad++

I am trying to change the order of the entries of an *.csv-file with Notepad++ built-in find/replace function. This is how the file looks like now: