There is an Access database and you need to take it and transfer it entirely with data in MS SQL.

You can, of course, copy line by line through the DataReader with single inserts, but this is slow.

In memory, I also do not want to load the entire DataTable from Access.

How to do it as quickly as possible?

  • "but this is slow" - do you keep such large tables in Access that speed is so important? - 4per
  • What is the size of the table? how many GB? If placed in memory, then you can quickly overtake via DataTable using BulkCopy. It takes from 7 seconds to 5 minutes for everything, depending on the amount of data and the workload of the target database. In extreme cases, import data to a file and BULK INSERT, which this file imports directly into the database. This can be done without Sharp. The format of the data file is given in the description of the command BULK INSERT - rdorn
  • @rdom, in theory, BulkCopy will do. One of the overloads WriteToServer takes on the input DataReader source. Does it still merge all the data from the source into memory (DataTable) or directly uploads to the server while maintaining high speed? - iluxa1810

1 answer 1

If you just need to transfer the data, and you need to do it through C #, then resorting to using the DataTable optional.

Streaming data loading in SqlServer is done using SqlBulkCopy , streaming reading from Access is OleDbDataReader using OleDbDataReader .

SqlBulkCopy can accept not only DataRow[] and DataTable , but also DbDataReader and IDataReader as DbDataReader . Because OleDbDataReader is one and the other; it is enough to submit the OleDbDataReader to the WriteToServer(...) method of the WriteToServer(...) instance:

 SqlConnection sqlConnection = ...; OleDbConnection oledbConnection = ...; using (var oledbCmd = new OleDbCommand(@" select Column1, Column2, ... from Table_Name", oledbConnection)) using (var oledbReader = oledbCmd.ExecuteReader()) using (var bulkCopy = new SqlBulkCopy(sqlConnection)) { bulkCopy.DestinationTableName = "dbo.TableName"; bulkCopy.EnableStreaming = true; bulkCopy.WriteToServer(oledbReader); }

A target table must of course be created before this.

Another option is to do without C #, using tools exclusively SqlServer. To do this, you need to install the Access DBMS driver in the same place where the SqlServer instance is installed and configure its use.

Then, using the OPENROWSET construct, OPENROWSET can access the Access database file and import data from the selected table with the SELECT ... INTO command (or INSERT INTO ... SELECT if the target table has already been created):

 SELECT t.Column1, t.Column2, ... INTO dbo.TableName FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'N:\AccessDbFiles\MsAccessDatabaseFile.accdb'; 'admin';'', [Table_Name]) AS t

Or through linked-server . We connect Access-base:

 EXEC sp_addlinkedserver @server = 'AccessDbName', -- алиас @srvproduct = 'Access', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = 'N:\AccessDbFiles\MsAccessDatabaseFile.accdb'

then we transfer the data to the necessary table:

 SELECT t.Column1, t.Column2, ... INTO dbo.TableName FROM [AccessDbName]...[Table_Name] AS t

Three points in [AccessDbName]...[AccessDb_Table_Name] - this is how it should be ( four-part naming , where the directory and schema are not specified).

Note that in this case the path to the Access DB file ( N:\AccessDbFiles\MsAccessDatabaseFile.accdb in the examples) is the path in the coordinates of the SqlServer instance (the path can be networked), and not in the coordinates of the host from which it is connected .

  • For the first option is it critical that the table being inserted and the target can differ in structure? For example, the target has similar types, but has additional attributes that are not in the inserted table. Is everything okay, and the missing columns will be filled with NULLs? - iluxa1810
  • one
    @ iluxa1810, if the number of columns is different, or the order in which they are followed, then according to the documentation , you need to set the mapping of the columns ( bulkCopy.ColumnMappings.Add(...) - there are options for name-to-name, name-to-index, index -to-name, index-to-index). Missing ones - yes, they should be filled with NULLs. - i-one
  • hmm, but I somehow forgot about DataReader ... thanks for reminding me. - rdorn