output to multiple files - 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

SQL Server 2000 Forums

Transact-SQL (2000)

output to multiple files

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-10 : 08:50:53

Hi,
I need to run a query each month that outputs data to various files...

my query is :

SELECT name, dob, address, setting from tbnames order by setting

what I need to do is create a new file for each setting so all people in setting 1 is in one file, all people in setting 2 is in another file and so on..
I have over 400 settings....

IF this possible via T-SQL then later run via a job ?>
idealy output to excel.

thank you for any info.. links, etc,

jamie

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-10 : 09:39:13

can easily run a bcp using a query to extract the data into different files.
CSV output will be opened by excel by default - otherwise you can use com, dts, reporting services to extract to excel.

==========================================
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.

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-11 : 05:24:23

Hi,
thank you.
but what will the query be that will break up the data for the files ?

regard,s
J

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-11 : 05:25:54

A select - the where clause will match the criteria you need.

==========================================
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.

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-11 : 05:58:07

but I need to do them all at the same time.
would I need to pass each number in the select statement one at a time the export ?
like :

@id int

set @ID = select distinct id from table

select data from table where id = @ID

run bcp command

loop here to run again with next number...

I think its the above part I need help with.
should ~I be looking at using cursors ?

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 09:25:12

You can call BCP from within SQL, so the SQL could do a loop for the 200 "settings" and call BCP with appropriate parameters for each one.

Something like:


DECLARE @id int,
        @intRowCount int
SET @intRowCount = 1, -- Force first iteration
    @id = -1          -- Impossible minimum value
WHILE @intRowCount > 0
BEGIN
   SELECT @ID = select MIN(id) from table WHERE id > @ID
   SELECT @intRowCount = @@ROWCOUNT

   IF @intRowCount = 1
   BEGIN
      run bcp command for @ID

      SELECT @ID = @ID + 1
   END
END

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-15 : 11:36:46

Hi,
I am working on this but get an error :
Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value 'e:\EY_Files\' to a column of data type int.

my code is as follows :

DECLARE @id int,
@intRowCount int,
@FileName varchar(100),
@bcpCommand varchar(2000)

SET @intRowCount = 1 -- Force first iteration
SET @id = -1 -- Impossible minimum value

WHILE @intRowCount > 0
BEGIN
SELECT @ID = MIN(Prov_No) from EY WHERE TermID=10 and Prov_No > @ID
SET @intRowCount = @@ROWCOUNT

IF @intRowCount = 1
BEGIN

--run bcp command for @ID

SET @FileName = 'e:\EY_Files\' + @ID + '.xls'

SET @bcpCommand = 'bcp "SELECT * FROM EarlyYears.dbo.EY WHERE TermID = 10 and Prov_No = @ID" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -f"' + '" -Usa -P'

EXEC master..xp_cmdshell @bcpCommand

SELECT @ID = @ID + 1

END
END

how can I convert the ID to a varchar so it can be added to the text file name ?
infact will this even work ?

regards,
J

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-15 : 11:48:55

SET @bcpCommand = 'bcp "SELECT * FROM EarlyYears.dbo.EY WHERE TermID = 10 and Prov_No = ' + convert)varchar(20),@ID) + '" queryout "'

==========================================
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.

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-15 : 11:52:54

Hi,
that is amazing.
it creates mulitple files.,
however, I now have the error :
Error = [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length

for each file....

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-15 : 12:00:06

ah, bcp terminators....
I have changed from f ( format file ) to -t. data now appears but is in a unrecognisable format..
I'll keep trying .thanks.

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-16 : 10:07:48

I give up.
is it possible to copy the data without using a format file into a csv. so when the csv is opened in excel it will display a field in each column. ?
am using -c but that puts each row into the A column...

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-17 : 08:17:53

Hi, I am now using a format file like so :

SET @FileName = 'e:\Data\' + convert(varchar(20),@ID) + '.csv'
SET @Formatfile = 'e:\EY_format.fmt'

SET @bcpCommand = 'bcp "SELECT Prov_No, forename, surname, convert(varchar,DOB, 103) AS DOB FROM EarlyYears.dbo.EY WHERE TermID = 10 and Prov_No = ' + convert(varchar(20),@ID) + '" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -f"' + @Formatfile + '" -Usa -P'

this is outputting the data to the correct files, but it is in an unrecognisable format.

all lines in my format file are like :
8 SQLCHAR 1 19 "" 8 DOB

all sqlchar why would some be showing jibberish text ?
also how can I get each field in a column and each row in a row ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-17 : 08:35:21

hi,
I have fixed it. !
need to use "'" in each line to move to next column !
now to fix the gobbldygook

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-11-17 : 09:28:39

Hi Kristen,
thank you for you help with this script, but I have only jsut noticed this runs forever. hoew can I stop it when the ID getss to 100000 ?

DECLARE @id int,
@intRowCount int
SET @intRowCount = 1, -- Force first iteration
@id = -1 -- Impossible minimum value
WHILE @intRowCount > 0
BEGIN
SELECT @ID = select MIN(id) from table WHERE id > @ID
SELECT @intRowCount = @@ROWCOUNT

IF @intRowCount = 1
BEGIN
run bcp command for @ID

SELECT @ID = @ID + 1
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources