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?
Asked
Active
Viewed 3.8k times
4 Answers
4
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.
Abe Miessler
- 82,532
- 99
- 305
- 486
-
good answer, using a normal TSQL sproc for this is definately not the way to go. – Anonymous Type Dec 13 '10 at 00:48
3
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 )
brian
- 3,635
- 15
- 17
-
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. – Cyber Slueth Omega Dec 14 '10 at 03:53
-
1bcp utility is for the command line (not for queries). However, you can use xp_cmdshell to run bcp from T-SQL. See here: http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell – brian Dec 14 '10 at 15:38
-
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). – brian Dec 14 '10 at 15:42
2
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:
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)
Justin
- 84,773
- 49
- 224
- 367
-
2Probably 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. – Chris J Dec 13 '10 at 09:45
