Add variable to data_file in bcp.exe

Posted on 2008-11-12
Last Modified: 2012-05-05
Hello Experts,

I have a bcp commend to export data from a query.  Is it possible to add a variable to the data_file (output file)?


wanting 20081112 to be a variable declared above.

"\\servername\folder\file_" + @run_date + ".txt"

Question by:thewayne73
    LVL 39

    Expert Comment

    how are you executing the bcp command?

    Accepted Solution

    using EXEC master..xp_cmdshell.  I did find a solution here:

    DECLARE @FileName varchar(50),
            @bcpCommand varchar(2000)

    SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

    SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'
    SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'

    PRINT @bcpCommand

    -- Results --

    bcp "SELECT * FROM pubs..authors ORDER BY au_lname"
      queryout "c:\authors_06-30-01.txt" -U garth -P pw -c

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now