Generate multiple result sets ( text files) based

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

SQL Server 2000 Forums

Transact-SQL (2000)

Generate multiple result sets ( text files) based

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-14 : 16:49:40

on the record number and unit

I have a table looks like

Id Unit

1 A01
2 A01
3 A01
4 A01
5 A01
6 A01
7 A02
8 A02
9 A02
10 A02
11 A02
12 A02

…

381989 A10

I would like to export the data to multiple text files which contains less than 20,000 records, but I don’t want the unit to over crossed the different text file
For example..
If A01 has 10000 records and A01 has 20000, I would like to export them to two text files: A01 (1000) in one file and A02 (20000) in another file
Not like
A01 (1000) + A02 (1000) in one file and A01 (10000) in another file.. and so on…

What is the best way to do this in SQL server?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 20:46:39

Something like this - haven't compiled it but you'll get the idea.
What should happen it a unit has more than 20000 rows?

select unit, num = count(*)
into #a
from tbl
group by unit

declare @sql varchar(8000)
declare @unit varchar(10), @lastunit varchar(10), @num int, @nextunit varchar(10)
select @lastunit = ''
while @lastunit < (select max(unit) from #a)
begin
select @unit = min(unit) from #a where unit > @lastunit
select @num = num from #a where unit = @unit
select @nextunit = @unit, @lastunit = @unit
while @num < 20000
begin
select @lastunit = @nextunit
select @nextunit = unit, @num = @num + num from (select top 1 * from #a where unit > @@nextunit order by unit) a
end
select @sql = 'bcp "select * from mydb..tbl where unit beteen ''' + @unit + ''' and ''' + @lastunit + ''"' queryout c:\myfile' + @unit + '.txt -c'
exec master..xp_cmdshell @sql
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-14 : 21:44:31

thanks,,,if a unit has more than 20000 , then generate another output( file) for that unit

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-15 : 14:24:57

here is what I am trying to do to export 20000 records each ( based on the unit_abbr)..I was using your soluton for this,but I must screwed up the quotations in the BCP part.. what am i doing wrong here?

I am getting an error:
Msg 102, Level 15, State 1, Line 64
Incorrect syntax near 'DETAIL'.
Msg 105, Level 15, State 1, Line 68
Unclosed quotation mark after the character string '' queryout c:\GL_Export' + @unit + '.txt -c'
exec master..xp_cmdshell @sql

here is the query
------------------------------------------------

select unit_abbr, num = count(*)
into #a
from GL_Export
group by unit_abbr
order by unit_abbr

declare @sql varchar(8000)
declare @unit varchar(10), @lastunit varchar(10), @num int, @nextunit varchar(10)
select @lastunit = ''
while @lastunit < (select max(unit_abbr) from #a)
begin
select @unit = min(unit_abbr) from #a where unit_abbr > @lastunit
select @num = num from #a where unit_abbr = @unit
select @nextunit = @unit, @lastunit = @unit
while @num < 20000
begin
select @lastunit = @nextunit
select @nextunit = unit_abbr, @num = @num + num from (select top 1 * from #a where unit_abbr > @nextunit order by unit_abbr) a
end
begin

select @sql = 'bcp "
select "Batch","","IC","PR","N"
union all
select '"DETAIL"'
+ ',"' + coalesce(ltrim(rtrim(unit_abbr)),'') + '"'
+ ',"' + coalesce(ltrim(rtrim(transaction_gl_code)),'') + '"'
+ ',' + coalesce(ltrim(rtrim(replace( convert(numeric(18,2),debit ), '.00', ''))),'')
+ ',' + coalesce(ltrim(rtrim(replace( convert(numeric(18,2),credit ), '.00', ''))),'') + ',' from GL_Export unit beteen ''' + @unit + ''' and ''' + @lastunit + ''"' queryout c:\GL_Export' + @unit + '.txt -c'

exec master..xp_cmdshell @sql

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources