SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

Fast importing of text files with format files and bulk insert

When creating SQL Server 2000 ETL solutions like importing of text files, it is common for developers to employ DTS packages to accomplish results quickly. The downside of this approach is that DTS packages have certain programmability limitations and don't always offer the best performance.

If you are importing very large raw text files that don't include formatting like column headers, a high-performance solution is to use BULK INSERT with format files.

For example, the format file may be named major.fmt (note the file extension .fmt) and would be placed in the same file system directory where the import source text files are located.

Here is a sample of what a format file looks like: 

8.0 4 1 SQLCHAR 0 0 "\"" 0 x SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 0 "\",\"" 1 WTN SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 0 "\",\"" 2 Major SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 0 "\"\r\n" 3 MktSeg SQL_Latin1_General_CP1_CI_AS


Look in SQL Server Books On Line to find more details about how to use format files.

Now you can perform BULK INSERTS from the source text file like this:


BULK INSERT Database_name..MajorAccount FROM '\\ServerName\Textfiles$\major.txt' WITH (FORMATFILE = '\\ServerName\Textfiles$\major.fmt')


Your data loads will now complete way faster than trying to accomplish the same results with DTS packages in SQL   Server 2000.

Hope this helps!

Print | posted on Wednesday, May 31, 2006 9:24 PM |

Feedback

Gravatar

# re: Fast importing of text files with format files and bulk insert

Hi,

The above doesn't work. It moves everything 1 column to the left...
Plus Column order should not start with 0.
1/11/2008 5:07 AM | Emmanouil Karaiskakis
Gravatar

# re: Fast importing of text files with format files and bulk insert

Sorry I meant 1 column to the right!!
1/11/2008 5:08 AM | Emmanouil Karaiskakis
Gravatar

# re: Fast importing of text files with format files and bulk insert

I have tried this but I am getting this error: "Column is too long in the data file for row 1, column 2. Make sure the field terminator and row terminator are specified correctly."

Why?
1/18/2008 10:26 AM | alice
Gravatar

# re: Fast importing of text files with format files and bulk insert

k245 29/02/2010 2341 dm332nenre 4 44kqw2q22w 243
k246 29/02/2010 2345 dm332nenre 4 44kqw2q22w 243
k247 29/02/2010 2343 dm332nenre 4 44kqw2q22w 243
k248 29/02/2010 2344 dm332nenre 4 44kqw2q22w 243


bulk insert in sql 2003 server.
i am faceing a problem to create format file.
pls tell any software that convert in format file .
1/26/2011 8:42 AM | Ashutosh
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET