Can I do SQL Server BULK INSERTs from Linux or UNIX?

If you want to load data into SQL Server quickly, you can use the SQL Server statement BULK INSERT. The SQL Server ODBC driver lets you do BULK INSERTs from Linux and UNIX machines:

  1. Create a text file containing the following data:
    1,Apple
    2,Orange
    3,Plum
    4,Pear
  2. Save the file as bulk.txt in a directory that's accessible through the local file system on the SQL Server machine. The file can be located in a local directory on the SQL Server machine or in a remote directory. SQL Server 2008 and later supports Universal Naming Convention (UNC) names for remote files.

    The example BULK INSERT statement in this article assumes the file is stored in the share \\192.168.0.27\myshare

  3. On the Linux or UNIX machine on which the SQL Server ODBC driver is installed, use isql to connect to the database you want to insert the data into. For example:
    $ cd /usr/local/easysoft/unixODBC/bin
    $ ./isql.sh -v MSSQL_DSN

    Replace MSSQL_DSN with the name of your SQL Server ODBC data source. If your ODBC data source doesn't contain your SQL Server login details, include them in the isql command line. For example:

    $ ./isql.sh -v MSSQL_DSN user pass
  4. Once connected, create a blank table:
    create table bulk_test ( testid integer, testname varchar(10) )
  5. Import the file bulk.txt by using the BULK INSERT statement. For example:
    BULK INSERT bulk_test FROM '\\192.168.0.27\myshare\bulk.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

    The Easysoft driver passes on the Transact-SQL, and so all the processing is done within SQL Server.

Further details of the BULK INSERT statement syntax can be found at http://msdn.microsoft.com/en-us/library/ms188365.aspx.