SQLCMD Output for batch file to inlude file names

Question

SQLCMD Output for batch file to inlude file names

cesimon25

SSC-Addicted

Points: 413
More actions
October 12, 2011 at 12:40 pm

#99487

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,

September 6, 2022 at 7:00 am

This was removed by the editor as SPAM

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

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

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

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.

cesimon25 SSC-Addicted Points: 413 More actions · Answer 2

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

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 3

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.

cesimon25 SSC-Addicted Points: 413 More actions · Answer 4

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.

PatrickOB Newbie Points: 2 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 6

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)