SQLCMD Output for batch file to inlude file names

  • Hi,

    I have a basic batch file that executes multiple SQL Scripts in a folder and exports the results to .csv file.

    Is there a way to include the file name in the output?

    For example in a folder I may have test1.sql, test2.sql, test3.sql etc

    I would like my output to have test1.sql then the results.

    I am able to get the results just fine, but just not the file name, any ideas?

    Thanks,

  • Hard to say without seeing your batch files, but I have run into difficulties with changing filenames both with bulk loading and bcp, so I just punt and do it outside of t-sql. Microsoft's implementation of t-sql just doesn't result in that good of a general purpose programming language.

  • for %%G in (*.sql) do sqlcmd /S SQLSRV02 /d Accounts -E -I -i "%%G"

    pause

    Above is the code. I am starting to think I may have to do this in SSMS, I actually started doing this in SSMS, below is the code basically created a temp table with the sql files then a while loop to go through the files and execute the sql, but stuck on how to merge the two!

    DECLARE@SQLCmdAS Varchar(8000)

    DECLARE@DBNameAS Varchar(255)

    DECLARE@Separator AS VARCHAR(1)

    DECLARE @FilePath AS VARCHAR(1000)

    DECLARE@Input AS VARCHAR(1000)

    DECLARE@dirsql AS VARCHAR(500)

    DECLARE@filetype AS VARCHAR(5)

    DECLARE@minfilenameID AS INT

    DECLARE @maxfilenameID AS INT

    DECLARE@filename AS VARCHAR(250)

    SET @filetype = '*.sql'

    SET @FilePath='\\Developer\Caroline\test6.csv'

    SET @Input='\\Developer\Caroline\Scripts\'

    SET @Separator=','

    SET @DBName='ThinkMoney'

    SET @dirsql = 'dir ' + @Input + '/b'

    CREATE TABLE #CDR

    (

    filenameID INT IDENTITY (1,1),

    filename VARCHAR(150)

    )

    CREATE TABLE #values

    (

    IDVARCHAR(500),

    FILENAMEvarchar(8000)

    )

    INSERT INTO #CDR (filename) EXEC master..xp_cmdshell @dirsql

    --Remove NULLs that are generated from the DIR command

    DELETE FROM #CDR WHERE filename is null

    --Modify filename so sqlcmd can run the scripts

    UPDATE #CDR

    SET filename = '\\Developer\Caroline\Scripts\' + filename

    SELECT * FROM #cdr

    SELECT @minfilenameID = MIN(a.filenameID), @maxfilenameID = MAX(a.filenameID)

    FROM #CDR a WITH (NOLOCK)

    --loop through the sql file to retrieve the clientid

    WHILE @minfilenameID <= @maxfilenameID

    BEGIN

    SELECT @filename = a.filename

    FROM #CDR a WITH (NOLOCK)

    WHERE a.filenameID = @minfilenameID

    --Export the data in text file

    SET @SQLCmd ='sqlcmd -d ' + @DBName + ' -i "' + @filename + '" -W -s "' + @Separator + '" -h +1'

    INSERT INTO #values (ID) EXEC master..xp_cmdshell @SQLCmd

    SELECT @minfilenameID = MIN(a.filenameID) FROM #CDR a WITH (NOLOCK)

    WHERE a.filenameID > @minfilenameID

    END

    DELETE FROM #values WHERE (ID IS NULL) OR (ID LIKE '%affected%') OR (ID LIKE '%-%')

    SELECT ID FROM #values

    UNION

    SELECT filename FROM #CDR

    DROP TABLE #values

    DROP TABLE #CDR

  • What I've done in the past is design my job as what I want to do with one file / one sql script, then put that complete job into another job that copies a varying file name into a constant file name, do my job, then copy the constant output filename into a varying file name.

    For instance using your example, my batch file might look like:

    for %%G in (*.sql) do call mysubroutine.bat "%%G"

    and the inner batch file could be as many lines as needed, like

    rem mysubroutine.bat

    copy %1 infile.txt

    sqlcmd /S SQLSRV02 /d Accounts -E-I -i infile.txt -o outfile.txt

    copy outfile.txt outputdir/%1

    or something to that effect, I'm sure thats not exactly right, but just tossing around an idea about how I'd approach iterating over a list of files / jobs.

  • Thanks, will try that and try to come up with other alternatives (this is turning out to be wayyy more complicated than I first anticipated!).

    The main problem is I am looking to execute over 100's of sql statements, all producing one row and one column so not much in the output.. more sql statements will just be added. Need something that is fairly streamline.

    Anyway if I find a solution will post it here.

  • I figured out a way to do this. You simply need to add the file name as apart of the for loop. I am outputing to a .txt file, but this should still work for you

     

    set ServerName=IP
    set DatabaseName=DB
    set username=user
    set password=pw
    set folder=C:\

    echo %date% %time% > %folder%\output.txt




    for /R "%folder%" %%i in (*.sql) do (
    ECHO %%~ni >> "%folder%\output.txt"
    SQLCMD -S %ServerName% -d %DatabaseName% -U %username% -P %password% -i %%i >> "%folder%\output.txt"
    )

    pause
  • Hi and welcome aboard. That's actually a really pertinent post for this 11 year old post and that's why I like this site so much. No "Comments are Closed" here! Well done and glad to "meet" you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply