CODEWITHSUNDEEP
c#sql-serverwinformsms-access
anfd
anfd

Reputation: 119

Add MS Access table data to SQL Server table with c#

I have a MS Access file with a Number table. Also I have a SQL Server database with a profile table.

How do I import data from the number table into the profile table?

Upvotes: 0

Views: 2546

Answers (1)

Piotr Stapp
Piotr Stapp

Reputation: 19828

It will be something like this:

    const string connectionString = "YOUR ACCESS CONNECTION STRING";
    const string connectionStringDest = "YOUR SQL SERVER CONNECTION STRING";
    using (var sourceConnection =
          new OleDbConnection(connectionString))
    {
        sourceConnection.Open();

        var commandSourceData = new OleDbCommand(
            "SELECT COL1, COL2 FROM TABLE_X;", sourceConnection);
        var reader =
            commandSourceData.ExecuteReader();

        using (var destinationConnection =
                   new SqlConnection(connectionStringDest))
        {
            destinationConnection.Open();

            using (var bulkCopy =
                       new SqlBulkCopy(destinationConnection))
            {
                bulkCopy.DestinationTableName =
                    "dbo.TABLE_DEST";

                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }
        }
    }

Upvotes: 4

Related Questions