During development you might want to pass a list of entities to a query or command. The recommended and safest way is to wrap every item in a parameter and pass it to the query. The problem is that the number of parameters that can be passed to a user function, stored procedure or even dynamic query is limited. The first two are set to 2100 entries per documentation. The third can vary based on the sql server version and the size of the actual parameters. In Sql Server 2008 it’s 65,536 * size of the network packet.
There are numerous way of dealing with this and I’ll describe two possible solutions and one how to not do this, so let’s start.
Bulk insert is one of the best SQL features whenever performance is needed. It’s simple and straightforward, the order of data in a table is retained from the file. However, what if we have already defined import files and would like to feed data from the bulk table in an ordered manner, without modifying files’ structure? To be sure that the ordered will be kept we need to somehow introduce a sorting column.
Io and behold, 3 simple ways to import from temp table and keep entries’ order.
Bulk insert to a view
One way to do this is to modify the original bulk table by adding a primary key. We should always add it in the first position – for two reasons: clarity and ability to extend table columns without dropping whole table.
Note: We’re going to use the simplest example as possible for readability.
First, we create/alter the import table by adding a primary key, which later will be used to determine the order of the insert.
CREATE TABLE ImportTemp(
iId int IDENTITY(1,1),
Then we create a view that contains all columns except identity.
CREATE VIEW ImportTempView
Finally simply perform the bulk insert to the view, results will be automatically inserted to the table on which view is based on.
BULK INSERT ImportTempView
It requires diving into the code base and adjusting the table, which is targeted during the import. Also, we add an additional abstraction layer plus performance overhead with the view itself.
We’re going to use the .NET SqlBulkCopyOptions to specify the way import feeds identification data. However, it requires modification of the import files, which can disqualify this solution (no access to the import files, too many templates, etc.).
public SqlBulkCopyAssistant CreateBulkCopy(string strDestinationTable)
return CreateBulkCopy(strDestinationTable, SqlBulkCopyOptions.KeepIdentity);
The SqlBulkCopyOptions.KeepIdentity option when turned on will allow feeding IDs from the import file. However, the import file needs to include ID values. If this option is disabled (bear in mind the SqlOptions use bitwise options), import file STILL needs to has the ID column specified, although it can be empty (letters in bold are just a header):
DataTable and DataColumn
The third and final method is based on .NET methods involving operations on DataView and DataTables.
using (var dataView = _csvDataReader.ReadDataWithHeaders(localFilePath))
var dataTable = dataView.ToTable();
var dataReader = dataTable.CreateDataReader();
Please focus on dataTable.Columns.Add(“iId”, typeof(int)).SetOrdinal(0); line, which is responsible for adding an empty column to the import table in the first position (0). This way we’re going to add a temporary dummy column on the fly and keep import clean without touching import files.
Whichever method you choose, you’ll end up having a temp table with identity column, which is both trivial and efficient to sort.