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, refer to 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' )