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?

Am I wrong or this ColumnMappings.Add is not necessary? The two tables are identical... — Svetlozar Angelov
– Svetlozar Angelov, Commented Aug 26, 2009 at 10:08

Adam Hughes · Accepted Answer · 2009-08-26 16:06:22Z

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

answered Aug 26, 2009 at 16:06

Adam Hughes

3,81226 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Achilles · Accepted Answer · 2010-07-10 07:55:11Z

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();
            }
        }
    }

That empty catch block is going to give you a big headache if there is an error and you don't know it. Maybe that's why you are saying "it works". Who knows?

Collectives™ on Stack Overflow

Import data from SQLite to SQL Server with SqlBulkCopy class

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related