CODEWITHSUNDEEP
sql-serverexportbcpexport-to-text
Jared H
Jared H

Reputation: 49

Using BCP in a SQL query

I have a stored procedure that updates several columns in a table and then selects out those columns so I can save them out for a report I run. Currently I am doing the exporting and saving manually though by setting column size in 'Query Options' to 333, turning off all the other options, putting the results to text and then saving the file out with the name and format. I know that there must be a way to automate this though the export and save process though.

I looked into using a job with Vb script, but this is a simple report that is run on demand and not on a schedule; so a job seemed to be overkill. The other option I found was BCP, which looks like what I need. I only know of this as a CMD tool though and can't quite figure out how to leverage this within a SQL query. In case it is needed here is an example of what my stored procedure is doing. Any help is appreciated and thank you in advance. I am using SQL 2008 R2 btw.

 UPDATE Table#1 SET Column1 = '' WHERE Column1 = 'XX'

UPDATE Table#1 SET Column2 = '' WHERE Column2 = '000000'



 SELECT   Column1 + 
          Column2 + 
          Column3  
 FROM Table#1 Where Column4 = 'T'

Upvotes: 2

Views: 2860

Answers (1)

Brad D
Brad D

Reputation: 762

BCP is a command line utility, however it can be access through SQL with the use of XP Command Shell so you will need xp_cmdshell enabled. The following is a parameterized example of how to call this inside of SQL

DECLARE @Server varchar(50) = 'ServerName' /* Source SQL Server */
DECLARE @FileName varchar(50) = ''
DECLARE @SourceProc varchar(50) = ''
DECLARE @sql varchar(8000)
DECLARE @RunDate varchar(10) = REPLACE(CONVERT(date, getdate()), '-','')
DECLARE @FilePath as varchar(255) = '\\UNCPathtoOutputFile\'
DECLARE @StartDate as Date = (select dateadd(month,datediff(month,0,GETDATE())-2,0))--'1/1/2013' /* Example of required Date as parameter */
DECLARE @EndDate as Date = (SELECT dateadd(month,datediff(month,0,GETDATE())-0,-1))--'2/1/2013' /* Example of required Date as parameter */
--  BCP Export
    SET @SourceProc = '[Schema].[StoredProcName]'
    SET @FileName = 'SomeOutputFile' + @RunDate + '.txt'
    SET @FilePath = @FilePath + @FileName
    SET @sql = 'bcp "EXEC [Database].' + @SourceProc + ' ''' + CONVERT(varchar(10),@StartDate, 101) + ''',''' + CONVERT(varchar(10),@EndDate, 101) + '''" QUERYOUT "' + @FilePath + '" -c -t"|" -r\n -S' + @Server + ' -T'

    --PRINT(@SQL)
    exec master.dbo.xp_cmdshell @sql

Upvotes: 3

Related Questions