You sometimes need to read the contents of a text file using SQL commands. You may want to read the results into a SQL result set or you may want to set the contents of the text file to a variable. xp_cmdshell is an extended stored procedure that enables SQL developers and SQL administrators to run dos commands on the underlying operating system. Let's assume that the name of the text file we want to read is sample.txt and it is located in the root folder of C drive. Open the command prompt by running the "cmd" command prompt on the RUN dialog box. On the command prompt screen type the below command shown as in the picture. type c:\sample.txt When you run the type command by pressing ENTER, the contents of the sample.txt text file will be displayed on the console. Now we will make our t-sql code to run this "type @filename" dos command successfully on the Query Analyzer by the help of the extended stored procedure xp_cmdshell
exec master.dbo.xp_cmdshell 'type c:\sample.txt'
set nocount on create table #file_contents ( line_number int identity, line_contents nvarchar(4000) ) declare @file_contents nvarchar(4000) declare @new_line char(2) set @new_line = char(13) + char(10) insert #file_contents exec master.dbo.xp_cmdshell 'type c:\sample.txt' select @file_contents = isnull(@file_contents, '') + @new_line + isnull(line_contents, '') from #file_contents drop table #file_contents select @file_contents set nocount off
We used the @new_line character while forming the @file_contents variable. If you do not use @new_line character all the contents of the text file will be returned as a single line. New line character or the line feed character enables the multi line structure of the original text file.
BlinkList Del.icio.us Digg Furl Simpy Spurl DZone ma.gnolia Shadows