Stored Procedure to Open and Read a text file

Question

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

Ethos

Answer 1

If the file is ready to load "as-is" (no data transformations or complex mappings required), 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

Tilth

Ok that is very userful. The first row would have the field names? Is there a way to save data using the bulk copy command. I have been trying to use the sqlcmd and bcp inside a stored procedure without much success to write a file. It seems much too complicated compared to a .net clr solution. But companies out in the wild seem to trust SPROCS and BCP more than .net CLR and the FileStream functions. Perhaps because of deployment issues. Thanks for the information with regard to Sql Server. – Ethos

bcp utility is for the command line (not for queries). However, you can use xp_cmdshell to run bcp from T-SQL. See here: sqlteam.com/article/… – Tilth

Also, remember that CLR sprocs are supported in 2005/2008, so you have that StreamWriter option. It just requires enabling CLR and trusting the assembly (slightly relaxing security settings). – Tilth

Answer 2

I would recommend looking at using SSIS. It's designed to do this sort of thing (especially if you need to do it on a regular basis).

Here is a good link that goes over reading a text file and inserting into the DB.

Peplos

good answer, using a normal TSQL sproc for this is definately not the way to go. – Ornithorhynchus

Answer 3

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

BULK INSERT is optimised for inserting large quantities of data and is intended to be used when the performance of a simple INSERT statement simply won't do.

If BULK INSERT isn't what you are after then you might want to take a look at the following article for a more straightforward technique:

• Reading and Writing Files in SQL Server using T-SQL

Linked in the article is a stored procedure uftReadFileAsTable which seems like it should be versatile enough to achieve what you are after.

If it isn't then you can at least use the stored procedure as an example of how to read files in SQL (it uses OLE / the Scripting.FileSystemObject)

Nilotic

Probably worth adding that the OLE procs (sp_OA*) are disabled by default as they can open up a lot of extra functionality, some of which may not be desirable, and as such should be treated as a security issue. – Gama

Answer 4

why don't use try user functions? This way you can use .NET to access and handle your file.

Check out this post

Mercedes