All the rows from my query result into a text field

Khan Consultoria
Khan Consultoria used Ask the Experts™
on
hello Guys,

I have a Query that results me 130.000 rows, I need to save all the rows into a txt file.
Today I use my system to do that, row by row and it takes me almost 3 horas processing.
Is there any possibility to have all the rows into a text field, thus I could save all the information
into a file in seconds.

Regards
Alexandre
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Check this article if this is what you need

Author

Commented:
Hi John,

I can't do this way, I need to return the information to my system as result into a dataset.

I was thinking about this, for exemplo. But I think it will not be fast

create procedure sp_SPED_Livro_G_Diario_Geral
begin 
  SELECT newid() as chave, 'N' as lido, * into #tmp FROM E001_2018_SPED_Livro_G_Diario_Geral a where 1=1  and (periodo in ('2018') or left(periodo,6) between '201801' and '201812') order by ORDEM_1,ORDEM_2,registro
 
  create table #t(id int, texto text)

  insert into #t (id, texto) values (1, '')

  declare @chave varchar(200);
  declare @linha varchar(1000);

  while exists (select top 1 'x' from #t where lido='N')
  Begin 
    select top 1 @chave=chave, @linha=registro from #tmp where lido='N';
    update #t set texto=texto + @linha where id=1
    update #tmp set lido='S' where chave=@chave
  End

  select * from #t
end

Open in new window


my first problem over there is using text field :(
I found a solution, as you can see bellow, the query return to my dataset in my application a text with all lines generated.


SELECT A.* 
			     FROM ( Select convert(text,     
							 REPLACE(  REPLACE(	 REPLACE(  REPLACE(  
																	(SELECT registro FROM E001_2018_SPED_Livro_G_Diario_Geral where periodo in ('2018') order by ORDEM_1, ORDEM_2, registro FOR XML PATH ('')) 
														   , '<registro>', '')  
												 , '</registro>', CHAR(13) + CHAR(10))
										 , '&#x0D;', '')
									  , '&lt;LINHAS&gt;', (SELECT convert(varchar, count(1)) FROM E001_2018_SPED_Livro_G_Diario_Geral where periodo in ('2018') ))) 
   				 AS LINHA ) AS A 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial