Batch Load Records from MS Access to SQL Server

[kheuer]

Hi,

I have an Access database with millions of records that I would like to store in a SQL server table. What type of input tool due I use to create this connection? I played around with the in-DB tools, but could not find the correct configuration.

Please help.

Kristina

[fmvizcaino · Accepted solution]

Hi kheuer,

To do that, you should use a tool called 'Input Data' and you should select to open an .accdb file. And to connect to sql server, you should use a tool called 'Output Data' and select a ODBC or OLEDB connection.

Best,

Fernando Vizcaino

[david_fetters]

For large inserts into SQL server, I would suggest using Alteryx's SQL Server Bulk connection.  When you click the dropdown in the Output Tool, instead of selecting SQL Server, select Other Databases >> SQL Server Bulk, as shown below:

Much like SQL servers traditional BULK insert methods, it can be orders of magnitude faster than a traditional insert because of minimal logging.

If you've never used the Bulk connection before, you'll need to use the ODBC Admin to setup the account in Alteryx.  Once the SQL Server Bulk Connection panel opens up:

1. Click "ODBC Admin" to open the ODBC Data Source Administrator panel
2. Under the User DSN tab click the "Add" button 
3. Select the driver you want to use (I generally pick the SQL Server Native Client 11)
4. Create a name and description, then add the server IP,Portadf
5. Enter your credentials on the next page
6. Select a default database
7. Click through the next page and hit Finish (you can mess with the other settings if your environment requires it)

Then you go back to the SQL Server Bulk Connection window in Alteryx and select the data source you just created, enter your username and password, and set your table.  Should be much quicker!

[BenMoss]

Just an FYI, if you dont have .accb in your dropdown on the input file types it will be because you dont have the appropriate drivers installed; it’s very easy to find online with a quick google