Stored procedure for opening and reading a text file

Question

I am looking for a stored procedure code that will open a text file, read several thousand lines and add code to a table in the database. Is there an easy way to implement this in T-SQL?

Answer 1

If the file is ready to be downloaded "as is" (there is no need for data conversion or complex comparisons), you can use the Bulk Insert command:

CREATE PROC dbo.uspImportTextFile

AS

BULK INSERT Tablename FROM 'C:\ImportFile.txt' WITH ( FIELDTERMINATOR ='|', FIRSTROW = 2 )

http://msdn.microsoft.com/en-us/library/ms188365.aspx

Answer 2

I would recommend using SSIS. It is designed for this kind of thing (especially if you need to do this on a regular basis).

Here is a good link that views reading a text file and pasting into a database.

Answer 3

The most efficient way to insert many records into a table is to use BULK INSERT (I believe that this is what BCP Utility uses, and therefore it should be just as fast).

BULK INSERT optimized for inserting large amounts of data and is intended to be used when the performance of a simple INSERT simply fails.

If BULK INSERT not what you need, then you can take a look at the following article for a simpler technique:

• Reading and writing files in SQL Server using T-SQL

The uftReadFileAsTable stored procedure associated with the article, which seems to have to be universal enough to achieve what you need.

If this is not the case, you can at least use a stored procedure as an example of how to read files in SQL (it uses OLE / Scripting. FileSystemObject )

Answer 4

Why not use try user functions? This way you can use .NET to access and process your file.

Mark this post