SQL Server BULK INSERT from Linux

Microsoft SQL Server's BULK INSERT function enables you to load data into SQL Server quickly.

The Easysoft SQL Server ODBC driver lets you take advantage of this SQL Server feature from Linux:

  1. On your Linux machine, download, install and license the SQL Server ODBC driver.

    For installation and licensing instructions, see the SQL Server ODBC driver product documentation.

  2. Create a SQL Server ODBC driver data source in odbc.ini that connects to the SQL Server instance into which you want to load the data. For example:
    [SQLSERVER_SAMPLE]
    Driver=Easysoft ODBC-SQL Server
    Description=Easysoft SQL Server ODBC driver
    Server=mymachine\myinstance
    User=mydomain\myuser
    Password=mypassword
    Database=mydatabase
  3. Use isql to create a SQL Server table to store the data. For example:
    $ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_SAMPLE
    SQL> CREATE TABLE BULK_TEST ( test_id integer, test_name varchar(10) )
  4. Create a file containing some data to insert into SQL Server:
    1,Apple
    2,Orange
    3,Plum
    4,Pear
  5. Save the file as bulk.txt and copy it somewhere the SQL Server instance can access the file.

    This can be on the SQL Server machine or on a remote shared folder. SQL Server 2008 and later supports UNC (Universal Naming Convention) paths as a means of locating the file.

    For our test, we placed the file on a Samba share:

    \\myhost\myshare
  6. Use isql to initiate the bulk insert. For example:
    SQL> BULK INSERT bulk_test FROM '\\myhost\myshare\bulk.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )