CODEWITHSUNDEEP
sql-serverdatabasesql-function
Azeem Akram
Azeem Akram

Reputation: 233

How to create text files of database rows?

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)

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

Upvotes: 1

Views: 4201

Answers (2)

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 3

Ram
Ram

Reputation: 823

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

Upvotes: 2

Related Questions