Multiple files extract - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple files extract

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-10 : 11:14:29

Hey guys,

In one of the SPs I created, creates a staging table and then a file is extracted using BCP Out. Requirement is changed now and they want me to extract different files (not 1 whole file) based on values in a field...I need to name the files according to that field value as well. Guys, can you please tell me the best way to do this... any sample script would be very helpful.

Thanks in advance!!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 11:52:03

quote:
Originally posted by GhantaBro

Hey guys,

In one of the SPs I created, creates a staging table and then a file is extracted using BCP Out. Requirement is changed now and they want me to extract different files (not 1 whole file) based on values in a field...I need to name the files according to that field value as well. Guys, can you please tell me the best way to do this... any sample script would be very helpful.

Thanks in advance!!!

Can you explain little more about this Please so we can help?

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-10 : 11:56:22

Thanks for the response...

Table has a column called key that has different set of values. I need to pull files for each key value... say for example if there are 300 records with 'AAAA' as key then I should have a file called ..AAAA.txt with 300 records plus files for other keys. Hope this makes sense.. Thanks again!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-10 : 11:56:51

the easiest way i can think of might be using CURSOR to loop through each type of file then BCP within the CURSOR.
here is the sudo:


---Get each file type from the cursor
declare cur cursor for
select <file type> from <table>
group by <file type>

open cur
declare @filetype varchar(100)

fetch next from cur into @filetype

while @@fetchnext = 0
--your BCP command with your query and filetype
fetch next from cur into @filetype
end
close cur
deallocate cur

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-10 : 13:50:53

thanks guys I guess cursor is the way I will go for.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-11 : 07:32:32

I am trying to use a variable @ert_stage created outside of the cursor in the cursor..I get error that need to declare @ert_stage... what is the work around for this guys? Should I use dynamic query for the select after declaring cursor? Thanks for your help...

declare cur cursor for
select [key] from @ert_stage
group by [key]

open cur
declare @key varchar(100)

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-11 : 08:31:09

I think this should work..

declare @sql varchar(1000)

Set @sql = 'declare cur cursor for
select [key] from ' + @ert_stage + ' group by [key] '
EXEC(@sql)
open cur
--------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources