With the SqlBulkCopy class, you may import data from SQLite to SQL Server.

sqlbulkcopy sqlite

Question

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?

1
1
8/26/2009 9:58:41 AM

Popular 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))
1
8/26/2009 4:06:22 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow