Output result to file for each cursor row
-
Hi there,
I would like to output the results of each cursor row into a separate file for the following statement:
USE SALES_DATA
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @company varchar(30)
DECLARE company_cursor CURSOR FOR
SELECT DISTINCT companyname
FROM COMPANY
ORDER BY 1
OPEN company_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM company_cursor
INTO @company
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Display the current values in the variables.
SELECT *
FROM CUSTOMERS
WHERE company = @company
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM company_cursor
INTO @company
END
CLOSE company_cursor
DEALLOCATE company_cursor
GO
Any help much appreciated.
Regards,
David
-
-
I don't mind which way it is done, as long as I can output each result set to a separate file.
Can this be achieved via BCP?
-
Yes, either bcp, or osql using the -o option, will output your data to a file. BCP is definitely the more configurable of the two.
--------------------
Colt 45 - the original point and click interface
-
Okay, so I've modified the script to utilise BCP, but when it executes it doesn't run successfully. Instead, it shows the usage commands for BCP for each results set, for example:
output
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
(12 row(s) affected)
This is based on the following syntax:
USE SALES_DATA
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @company varchar(30)
-- Declare BCP variables for data output
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
DECLARE company_cursor CURSOR FOR
SELECT DISTINCT companyname
FROM COMPANY
ORDER BY 1
OPEN company_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM company_cursor
INTO @company
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set file location and name
SET @FileName = REPLACE('c:\sales_'+@company+'.csv','/','-')
-- Set BCP command
SET @bcpCommand = 'bcp "SELECT * FROM CUSTOMERS WHERE company = @company" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -t -T -S'
-- Execute BCP command
EXEC master..xp_cmdshell @bcpCommand
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM company_cursor
INTO @company
END
CLOSE company_cursor
DEALLOCATE company_cursor
GO
Anything stand out as to why this is occurring?
-
The BCP won't know what the @company variable is. You'll need to build the sql statement prior to giving it to BCP.
eg: set @sql = 'SELECT * FROM CUSTOMERS WHERE company = ''' + @company + '''' --Note the number of quotes
You'll also have to tell BCP which server to login into and provide appropriate credentials.
--------------------
Colt 45 - the original point and click interface -
Thanks for the reply,
I've got things working now - if only there was an option to output column names in BCP...
-
You could sort of hack that together by doing a union query with the first part holding the column names.
select 1 as Seq, 'yourColumn_1' as yourColumn_1
union
select 2 as Seq, bcpColumn
from ...
order by Seq
This post has a generic query to generate the statement,
http://www.dbforums.com/showthread.php?t=940036
--------------------
Colt 45 - the original point and click interface
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply