none
Bulk inserting between databases RRS feed

  • Question

  • Hi

    Is it possible to bulk insert records from an MS Access table into a SQL Server table? Is it, for instance, possible to execute an INSERT query that refers to the two tables in separate databases? Any help would be appreciated.

    Thanks

    Regards

    Yahya

    Wednesday, February 1, 2012 8:52 AM

Answers

  • Yes BulkInsert From MS-Access to SQL is possible using SqlBulkCopy Class of C#

    Read Data From MS-Access Table and fill it in a Ado.net DataTable say dtblNew

    Create a SQL Connection Say destConnection and use it as below,

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection)) 
           
    { 
                bulkCopy
    .ColumnMappings.Add("ColumnName", "ColumnName"); 
                bulkCopy
    .ColumnMappings.Add("ColumnName", "ColumnName"); 
            bulkCopy
    .DestinationTableName = "DBTableName"; 
            bulkCopy
    .WriteToServer(dtblNew); 
       
    } 

    To know more about SqlBulkCopy refer,

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

    Insert can only operate on one table at a time. Multiple Inserts have to have multiple statements with <DatabaseName.TableName> or ServerName.DatabaseName.TableName if LinkedServer is Configured.

    Or another approach would be,

    Create a View selecting the column names required by your insert statement, add an INSTEAD OF INSERT Trigger, and insert into this view.


    Lingaraj Mishra
    • Proposed as answer by Alan_chenModerator Monday, February 6, 2012 2:10 AM
    • Marked as answer by Y a h y a Monday, February 6, 2012 7:04 AM
    Wednesday, February 1, 2012 11:03 AM
  • In addition to the SqlBulkCopy Class you can also use a SQL statement:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                "Data Source=c:\test files\db1 XP.mdb")
    
    
            AccessConnection.Open()
    
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Table1] SELECT * FROM AccessTable")
    
            AccessCommand.ExecuteNonQuery()
            AccessConnection.Close()
    
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Proposed as answer by Alan_chenModerator Monday, February 6, 2012 2:10 AM
    • Marked as answer by Y a h y a Monday, February 6, 2012 7:04 AM
    Wednesday, February 1, 2012 2:30 PM

All replies

  • Yes BulkInsert From MS-Access to SQL is possible using SqlBulkCopy Class of C#

    Read Data From MS-Access Table and fill it in a Ado.net DataTable say dtblNew

    Create a SQL Connection Say destConnection and use it as below,

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection)) 
           
    { 
                bulkCopy
    .ColumnMappings.Add("ColumnName", "ColumnName"); 
                bulkCopy
    .ColumnMappings.Add("ColumnName", "ColumnName"); 
            bulkCopy
    .DestinationTableName = "DBTableName"; 
            bulkCopy
    .WriteToServer(dtblNew); 
       
    } 

    To know more about SqlBulkCopy refer,

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

    Insert can only operate on one table at a time. Multiple Inserts have to have multiple statements with <DatabaseName.TableName> or ServerName.DatabaseName.TableName if LinkedServer is Configured.

    Or another approach would be,

    Create a View selecting the column names required by your insert statement, add an INSTEAD OF INSERT Trigger, and insert into this view.


    Lingaraj Mishra
    • Proposed as answer by Alan_chenModerator Monday, February 6, 2012 2:10 AM
    • Marked as answer by Y a h y a Monday, February 6, 2012 7:04 AM
    Wednesday, February 1, 2012 11:03 AM
  • In addition to the SqlBulkCopy Class you can also use a SQL statement:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                "Data Source=c:\test files\db1 XP.mdb")
    
    
            AccessConnection.Open()
    
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Table1] SELECT * FROM AccessTable")
    
            AccessCommand.ExecuteNonQuery()
            AccessConnection.Close()
    
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Proposed as answer by Alan_chenModerator Monday, February 6, 2012 2:10 AM
    • Marked as answer by Y a h y a Monday, February 6, 2012 7:04 AM
    Wednesday, February 1, 2012 2:30 PM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 6, 2012 2:10 AM
    Moderator