asked
how to export all data from one table to multiple text files
I have a table and want to create an SSIS package to create multiple text file with 200 rows of data in each file. I have used row_number but unable to put the row_number value in a loop to process the next 200 rows. Any ideas on how to use the row_number value in a between clause.
Below is the code I implemented and will use ssis foreach loop to create the files. DECLARE @NoOfRecords AS INT = 200 DECLARE @WhichPage AS INT = 0 WHILE (select COUNT(*) from [batch_hl7] where batchname is null) <> 0 begin ;WITH CTE AS ( SELECT * , ROW_NUMBER() OVER(ORDER BY id DESC) rnk FROM [batch_hl7] ) update CTE set batchname = 'batchname'+ CAST(@whichpage as varchar(100)) WHERE Rnk >= (( @WhichPage * @NoOfRecords ) + 1 ) AND RNK <= ( ( @WhichPage + 1 ) * @NoOfRecords ) set @WhichPage = @WhichPage+1 end set @WhichPage = @WhichPage+1
18 People are following this question.
