SQL ordered insert from bulk

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),
[City] nvarchar(30)
)

Then we create a view that contains all columns except identity.

CREATE VIEW ImportTempView
AS 
SELECT 
    city
FROM ImportTemp

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  
   FROM 'Q:\cities.txt';   

Disadvantages?

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.

SqlBulkCopyOptions

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.).

Example:

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):

id, city

,toronto

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();
       dataTable.Columns.Add("iId", typeof(int)).SetOrdinal(0);
       var dataReader = dataTable.CreateDataReader();
      _bulkUploadService.Upload(ProcessImport.LoadTempTableName, dataReader);
    }

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.