Batch Blocking in SQL Table

Question

Post reply

Batch Blocking in SQL Table

Arindam Basu

Valued Member

Points: 58
More actions
July 14, 2006 at 12:20 am

#113726

One requirement if anyone can help would be appreciated.
A table having 5 Fields and doesn't have unique data. I want to fetch data from that table in batches. How can I achieve the same.
Say the table has ten thousand records and I want to fetch the same in batches of say 100 or 200..so that it can be put to a flat file on 100 and 200 for 100 or 50 flatfiles.
If this can be achieved by just simple query

Arindam Basu
Software Engineer
Hewlett Packard India Pvt Ltd.
Bangalore-17

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

balde SSCommitted Points: 1594 More actions · Answer 1

Hi Arindam,

you could try this;

DECLARE @bcpResult int

DECLARE @bcpCommand nvarchar(4000)

DECLARE @ifile_id integer

DECLARE @sfile_id varchar(3)

create table ##temp

(

col1, -- as <source_table>

col2, -- as <source_table>

col3, -- as <source_table>

col4, -- as <source_table>

col5, -- as <source_table>

col6 uniqueidentifier -- new uid column

col7 int -- new for file_id

)

insert ##temp

select col1, col2, col3, col4, col5, newid() as col6, 0 as col7

from <source_table>

while @file_id < 100

begin

update ##temp

set col7 = @file_id

where col6 in ( select top 100 col6 from ##temp )

and col7 = 0

SET @sfile_id = convert(varchar(4), @file_id)

SET @bcpCommand = 'bcp "select col1,col2,col3,col4,col5 from ##temp where col7 = ' + @sfile_id + '" queryout "C:\test\file_' + @sfile_id + '.csv" -P -t"," -c'

-- Export to CSV

EXEC @bcpResult = master..xp_cmdshell @bcpCommand

set @file_id = @file_id + 1

end

any problems with this let me know,

cheers

Paul

Arindam Basu Valued Member Points: 58 More actions · Answer 2

Hi Paul, Thanks for the same ..it did worked I am able to get the output on dry run with the temptable but the output to the csv file is not getting generated. Any idea ..there is no error as such

Arindam

Arindam Basu
Software Engineer
Hewlett Packard India Pvt Ltd.
Bangalore-17

balde SSCommitted Points: 1594 More actions · Answer 3

Have you used bcp successfully before? If not it may be a permissions issue. If bcp is attempting to write the files you should be getting some feedback from Query Analyser.

Check also the path exists where you are attempting to dump the csv files too.