CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 2472

SQL Server : how to dump/extract each row to separate file

I have a business requirement to take a table with columns PrimaryKey, A, B, C, D, E and dump each to a file such as:

filename:  Primarykey.txt
(row 1) A
(row 2) B
(row 3) C

etc.

Is there a good way to do this with SQL Server 2008 or should I write a C# program using a datatable? The table I am using has about 200k rows in it.

Thanks

Upvotes: 1

Views: 6374

Answers (2)

Jed Schaaf
Jed Schaaf

Reputation: 1085

Using xp_cmdshell

If xp_cmdshell is permitted, you might be able to use something like this:

declare @path varchar(200)
declare @schema varchar(100)
declare @pk varchar(100)
declare @sql varchar(2000)
declare @cmd varchar(2500)
set @path = 'C:\your\file\path\'
declare rowz cursor for (select PrimaryKey from TableA)
open rowz
fetch next from rowz into @pk
while @@FETCH_STATUS = 0
begin
    set @sql = 'select A, B, C, D, E from TableA where PrimaryKey = ''' + @pk + ''''
    set @cmd = 'bcp "' + @sql + '" queryout "' + @path + @pk + '.txt" -T -c -t\n'
    exec xp_cmdshell @cmd
    fetch next from rowz into @item
end
close rowz
deallocate rowz

Using sqlcmd

Alternately, you can create a series of statements to run through sqlcmd to create the separate files.

First, create a temporary table and add some boilerplate entries:

create table #temp (tkey int, things varchar(max))
insert into #temp (tkey, things) values (0, 'SET NOCOUNT ON;
GO'),(2, ':out stdout')

Then, fill the temporary table with the queries that we need:

declare @dir varchar(250)
declare @sql varchar(5000)
declare @cmd varchar(5500)
declare @schema varchar(100)
declare @pk varchar(100)
set @schema = 'YourSchema'
declare rowz cursor for (select PrimaryKey from "YourSchema"..TableA)
open rowz
fetch next from rowz into @pk
while @@FETCH_STATUS = 0
begin
    set @dir = '"C:\your\file\path\'+@pk+'.txt"'
    set @sql = '
SELECT A +''
''+ B +''
''+ C +''
''+ D +''
''+ E
FROM "'+@schema+'"..TableA where PrimaryKey = '+@pk
    set @cmd = ':out '+@dir+@sql+'
GO'
    insert into #temp (tkey,things) values (1, @cmd)
    fetch next from rowz into @pk
end
close rowz
deallocate rowz

Next, query the temporary table to get the generated queries:

select things from #temp order by tkey

Finally, copy the results to a file and send this file as input to sqlcmd.exe at the command prompt with the parameter -h -1:

C:\your\file\path>sqlcmd -h -1 -S YourServer -i "script.sql"

Upvotes: 1

SteveB
SteveB

Reputation: 1514

The links below contain some previous posts and another link to a possible solution using SSIS.

You might have to play around until you get what you want.

Good luck.

Some clues here

or SQL Server Forums - Create multiple files from SSIS

Upvotes: 1

Related Questions