I'm trying to generate a .csv based on a query and stumbled across this particular command (bcp) while googling. While I'm able to run the following code successfully, the query you see is just a sample one I created just for testing. The actual query I need to run involves lots of tickmarks and is somewhat larger, I think it best to create a separate variable and then plug into the command string but I'm having trouble with syntax, continuation, and that sort of thing.
DECLARE @FileName varchar(50),
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.csv','/','-')
SET @bcpCommand = 'bcp "SELECT name FROM empmast..empmast ORDER BY name" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U testlogin -P pw -c'
exec master..xp_cmdshell @bcpCommand
I want to isolate the query and then build it into the @bcpCommand string but unfortunately the query is like this:
SET @query = "select empno as 'External id', f_name as 'FIRST_NAME', a.l_name as 'LAST_NAME', " +
"case when a.empl_no in ('608428','601199','601329') else 'N', " +
"from empmast..empmast "
SET @bcpCommand = 'bcp ' + @query + ' queryout '"
Can I do this? Your help is greatly appreciated.