LIULIHUA
asked on
How to make a txt file export from this UDF?
Hi experts,
I have a UDF be created, now I want to export the returned table to txt type file to my C:\data, how can I do it? thanks.
/**** Create UDF****/
CREATE FUNCTION LargeOrder ( @Quantity int )
RETURNS @OrderShipperTab TABLE
(
INVOICE_ID int,
PRODUCT_ID nvarchar(10),
QUANTITY int,
TOTAL_PRICE int,
ACCOUNT_ID varchar (12)
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT C.INVOICE_ID, C.PRODUCT_ID,
C.QUANTITY, C.TOTAL_PRICE,C.ACCOUNT_ID
FROM CA_PURCHASES C
WHERE C.QUANTITY > @Quantity
RETURN
END
GO
/***** the table returned by the function**/
SELECT * FROM LARGEORDER (500)
GO
I have a UDF be created, now I want to export the returned table to txt type file to my C:\data, how can I do it? thanks.
/**** Create UDF****/
CREATE FUNCTION LargeOrder ( @Quantity int )
RETURNS @OrderShipperTab TABLE
(
INVOICE_ID int,
PRODUCT_ID nvarchar(10),
QUANTITY int,
TOTAL_PRICE int,
ACCOUNT_ID varchar (12)
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT C.INVOICE_ID, C.PRODUCT_ID,
C.QUANTITY, C.TOTAL_PRICE,C.ACCOUNT_ID
FROM CA_PURCHASES C
WHERE C.QUANTITY > @Quantity
RETURN
END
GO
/***** the table returned by the function**/
SELECT * FROM LARGEORDER (500)
GO
ASKER
i am new, would you please list the code step by step, much thanks.
If this is a one-time thing and you want a csv, you can just use query analyzer. Set the query results to be in a grid, do your select, right click the upper left box in the grid and select "save as"
ASKER
No, this gona to be set up in DTS Execut SQL task to run automatically when the scheduel due.
I just found a code from this site as following, but it gave me message as below.
/**** BCP*****/
EXEC master..xp_cmdshell 'bcp ""SELECT * FROM Largeorder (500) "" queryout ""c:\data\export.csv"" -U uname -P password -c -t,'
/**** out put****/
Copy direction must be either 'in', 'out' or 'format'.
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"]
NULL
I just found a code from this site as following, but it gave me message as below.
/**** BCP*****/
EXEC master..xp_cmdshell 'bcp ""SELECT * FROM Largeorder (500) "" queryout ""c:\data\export.csv"" -U uname -P password -c -t,'
/**** out put****/
Copy direction must be either 'in', 'out' or 'format'.
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"]
NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works, thanks.
from command prompt