Bcp and long query

Transact-SQL
1

Hi all,

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),
@bcpCommand varchar(2000)

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.

2

fralo, what is your final aim with this csv file? there might much easier ways of doing this other than bcp
is this csv to be consumed by another process? are you emailing this to an external user?
can you please explain your end game what you plan to use this csv for?

3

Yes, the ultimate aim is to send this .csv to another vendor. We have in place how that will happen once the file is created. For now, all I need is to generate the file. And I have a massive query ready to do it. If you can suggest a better method I would greatly appreciate it. This will need to be an automated nightly process so I was gonna simply stick this code into a stored procedure and schedule it to run.

4

Not necessarily better way but ..

  1. SSRS subscriptions
  2. SQL Job (like you said)
  3. SSIS

are the few other ways. bcp is cool too but worth knowing there are other ways to do it.

5

I would recommend using SSIS - as that process will allow you to simplify the query (no need to worry about single/double quotes) or quoting column data (double quotes for strings that may contain a comma), etc...

SSIS will also allow you to copy the file to the destination - or use FTP (SFTP) to transfer the file as well as archive the file once completed. You can build into the process tracking if needed - automatic renaming of files with a date stamp, etc...

1 Like