How to create text files of database rows?

Question

I have a database table with a column named File Content, and many rows. What I need is to create a text file for each row of File Content column.

Example:

Sr.        File Name        File Content

1.         FN1              Hello
2.         FN2              Good Morning
3.         FN3              How are you?
4.         FN4              Where are you?

Suppose I have 4 rows, then 4 text files should be created (maybe with any name which we want)

File1.txt should have text "hello" in it.
File2.txt should have text "Good Morning" in it.
File3.txt should have text "How are you?" in it.
File4.txt should have text "Where are you?" in it

What SQL? Microsoft SQL Sever? MySQL? Oracle? SQLite? Postresql? ... — Mat
– Mat, Commented Jun 2, 2012 at 12:10

Pondlife · Accepted Answer · 2012-06-02 14:16:57Z

3

Although you said you said you need to do it in TSQL, I wouldn't do it that way if possible. Ram has shown you one solution, but it has the disadvantages that you need to use xp_cmdshell and the SQL Server service account needs permission to access the file system in whatever location you want to have the files.

My suggestion would be to write a script or small program in your preferred language (PowerShell, Perl, Python, C#, whatever) and use that instead. TSQL as a language is simply badly suited for manipulating files or handling anything outside the database. It is obviously possible (CLR procedures are another way), but you often run into problems with permissions, encodings and other issues that are much easier to deal with in an external language.

answered Jun 2, 2012 at 14:16

Pondlife

16.3k6 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ram · Accepted Answer · 2012-06-02 13:40:59Z

This can be done with BCP OUT syntax of SQL server.

For the setup: just make sure that you have xp_cmdshell exec permissions on the server. This can be checked from master.sys.configurations table. Also change filelocation path corresponding to your server or network share. I checked and was able to generate 4 files as there are 4 records in the table.

use master
go

declare @DSQL Nvarchar(max)
declare @counter int
declare @maxrows int
declare @filename Nvarchar(30)

select @counter=1, @maxrows = 0

create table t1 (
 sno int identity(1,1) not null,
 filename varchar(5),
 filecontent varchar(100)
)

insert into t1
select 'FN1', 'Hello'
UNION
select 'FN2', 'Good Morning'
UNION
select 'FN3', 'How are you?'
UNION
select 'FN14', 'Where are you?'

select @maxrows = count(*) from t1

--SELECT * FROM T1

while (@counter <= @maxrows)
begin
  select @filename = filename from t1
   where sno = @counter
select @DSQL = N'exec xp_cmdshell' + ' ''bcp "select filecontent from master.dbo.T1 where sno = ' + cast(@counter as nvarchar(10)) + '" queryout "d:\temp\' + @filename + '.txt" -T -c -S home-e93994b54f'''

print @dsql
exec sp_executesql @DSQL
   select @counter = @counter + 1
end

drop table t1

Collectives™ on Stack Overflow

How to create text files of database rows?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related