Sqlite – Import data from SQLite to SQL Server with SqlBulkCopy class

sqlbulkcopysqlite

I'm trying to transfer the data from SQLite to SQL Server. The schema of target and destination table are just the same:

SQL Server:

CREATE TABLE [dbo].[Shop] (
    [ShopID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Url] [nvarchar](128) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ShopID] ASC
))

and SQLite:

CREATE TABLE "Shop" (
    "ShopID" INTEGER PRIMARY KEY  NOT NULL,
    "Name" VARCHAR NOT NULL,
    "Url" VARCHAR NOT NULL);

I wrote the code as below (with System.Data.SQLite):

using (var conn = new SQLiteConnection(@"Data Source=Data.sqlite;FailIfMissing=True"))
{ 
    conn.Open();
    var cmd = new SQLiteCommand("SELECT * FROM Shop", conn);
    var reader = cmd.ExecuteReader();

    using (var bulkCopy = new SqlBulkCopy("Data Source=.;Initial Catalog=Test;Integrated Security=True"))
    {
        bulkCopy.DestinationTableName = "Shop";
        bulkCopy.ColumnMappings.Add("ShopID", "ShopID");
        bulkCopy.ColumnMappings.Add("Name", "Name");
        bulkCopy.ColumnMappings.Add("Url", "Url");
        bulkCopy.WriteToServer(reader);
    }
}

Data has been loaded by reader (I've checked). But an InvalidOperationException throws on WriteToServer method: The given ColumnMapping does not match up with any column in the source or destination.

Any ideas or suggestion for me?

Best Answer

This may or may not solve your problem, but you probably want to use the SqlBulkCopyOptions to specify that you don't want it to generate new identity values.

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
Related Topic