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