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?
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"
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@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 @dirsql = 'dir ' + @Input + '/b'
CREATE TABLE #CDR
filenameID INT IDENTITY (1,1),
CREATE TABLE #values
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
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
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
DELETE FROM #values WHERE (ID IS NULL) OR (ID LIKE '%affected%') OR (ID LIKE '%-%')
SELECT ID FROM #values
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
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
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"
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.
Change is inevitable... Change for the better is not.
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)