Migrating Database Using Bulk Insert

Question

I am writing a console application to migrate an application's data from SQLite to SQL Server. I installed the Dapper Plus package to try and speed up the transfer of data from one to the other. The issue I'm running into is, I want the primary keys to transfer with each table so that all database relationships remain intact. However, when I use the BulkInsert operation, it just uses the autogenerated identity (1, 2, 3, etc.).

I've imported the existing data into a List<DataSources>, with the Ids from the exiting table. I tried setting the AllowUpdatePrimaryKeys option to true, but it is not inserting the records with the existing Id.

using IDbConnection connection = new SqlConnection(_sqlServerConnectionString);
connection.UseBulkOptions(o =>
{
    o.AllowUpdatePrimaryKeys = true;
}).BulkInsert(dataSources);

Answer 1

The required option is InsertKeepIdentity.

using IDbConnection connection = new SqlConnection(_sqlServerConnectionString);
connection.UseBulkOptions(opts =>
{
    opts.InsertKeepIdentity = true;
}).BulkInsert(dataSources);