Bulk Insert Using OPENROWSET()

I needed to bulk import a text file to a table but I couldn’t use BULK INSERT as the text file was only for a few columns that were present in the destination table.  Rather than bulk insert to a new temporary table and insert the data from here, I looked into using the OPENROWSET() function to bulk insert simply because I’ve not used this process before and I knew that you could.

First I created a text file that held the data I wanted to Insert:

AAA       DAZ        UC418   Team1 HB          Assess

DBA       KENZ     UC418   Team2 HB          Assess

KEOA     PISZ       UC418   Team3 HB          Assess

PITA       ZZZ         UC418   Team4 HB          Assess

I then created a Format file that held the format details for the text file. This is another Tab delimited text file:

9.0
6
1    SQLCHAR    0    24    “\t”    1    Low_Range    Latin1_General_CI_AI
2    SQLCHAR    0    24    “\t”    2    High_Range    Latin1_General_CI_AI
3    SQLCHAR    0    8    “\t”    3    Doc_Type    Latin1_General_CI_AI
4    SQLCHAR    0    10    “\t”    4    Team    Latin1_General_CI_AI
5    SQLCHAR    0    2    “\t”    5    File_System    Latin1_General_CI_AI
6    SQLCHAR    0    10    “\r\n”    6    Role    Latin1_General_CI_AI

1) 9.0 is the SQL version (in this case it is SQL Server 2008R2)

2) 6 is the number of columns I am importing

3) First column can hold 0 – 24 Chars, Second Column 0 – 2 etc

4) “\t” – Each column is separated by a tab except the last column which is separated by a new line (“\r\n”).

5) 1 This is the column number

6) Low_Range – This is the table column name that you are inserting the data to.

7) Latin1_General_CI_AI – This is the collation type.

8) Make sure your format file ends with a blank line or the insert will fail!

Next you will need to copy the import file and the format file to your server for import and then the following Insert Query should insert the data to your table:

INSERT INTO Mail_Routing(Low_Range, High_Range, Doc_Type, Team, File_System, ROLE)
SELECT document.* FROM openrowset(BULK '\\ServerName\ServerDirectory$\BulkInsert\UCDocRout.txt',
formatfile='\\ServerName\ServerDirectory$\BulkInsert\MailRout.fmt.txt',
firstrow=1) AS document