Import data from SQLite to SQL Server with SqlBulkCopy class

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?

Answer 1

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

Answer 2

This works for me...

private void GatherDb3Info(FileInfo[] fiDb3) {
        SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder();
        foreach (FileInfo fi in fiDb3) {
            csb.Clear();
            csb.DataSource = fi.FullName;
            csb.Password = "P@$$w0rd";
            csb.SyncMode = SynchronizationModes.Full;

            using (var conn = new SQLiteConnection(csb.ToString())) {
                conn.Open();
                DataTable dtTables = conn.GetSchema(SQLiteMetaDataCollectionNames.Tables, new String[] { });
                foreach (DataRow dRow in dtTables.Rows) {
                    if (dRow["Table_Type"].ToString().ToLower() != "table") continue;
                    String
                        catName = String.Format("{0}", dRow["Table_Catalog"]),
                        schName = String.Format("{0}", dRow["Table_Schema"]),
                        tblName = String.Format("{0}", dRow["Table_Name"]);
                    DataTable dtColumns = conn.GetSchema(SQLiteMetaDataCollectionNames.Columns, new System.String[] { catName, schName, tblName });
                    StringBuilder sb = new StringBuilder();
                    foreach (DataRow dRowColumn in dtColumns.Rows) {
                        sb.AppendFormat("[{0}], ", dRowColumn["Column_Name"]);
                    }

                    String sColList = sb.ToString();
                    sColList = sColList.Remove(sColList.Length - 2);
                    var cmd = new SQLiteCommand("Select " + sColList + " From " + tblName, conn);
                    var reader = cmd.ExecuteReader();
                    using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(@"Server=.;Integrated Security=true;Database=TargetDBName;")) {
                        bulkCopy.DestinationTableName = "TargetTableSchema." + tblName;
                        try {
                            bulkCopy.WriteToServer(reader);
                        } catch (Exception) { }
                    }
                }
                conn.Close();
            }
        }
    }