January 23, 2009 at 3:14 pm
Hi all,
I am trying to create text file or any out put file in certain directory straight from sql query:
Select top 50 * from table1 to file!!
How can i do that!!
January 23, 2009 at 3:20 pm
use bulk insert and point to location where you want to store the output of query .
January 23, 2009 at 3:21 pm
I'd look at the BCP utility, which would give you a LOT of options as to how to output the query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2009 at 3:32 pm
I tried but it only creates value and then says this:
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('E:\temp\result_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "select top 50 name, reward, address, number, contact from table1" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T '
EXEC master..xp_cmdshell @bcpCommand
and in result panel is shows this:
NULL
Enter the file storage type of field name[nvarchar]:
Why????
January 25, 2009 at 9:29 pm
When using bcp, you have to specify the data types in some format. For faster responses, try -n.
-n: Native
-c: character
-N: native (database) data types of the data for noncharacter data, and Unicode characters for character data
-w: using Unicode characters
Take a look at this msdn for more details.
http://msdn.microsoft.com/en-us/library/ms162802.aspx
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
January 25, 2009 at 9:50 pm
If you need it for a job, then you can also use a regular select query and output the results to a file. Check out the advance tab in the step editing window.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply