Transaction with multiple SQLCMD calls in a batch file

Is there a good way to do something like the following?

SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION"
FOR %%f in (script1 script2 script3) do (
    SET CURRENT_SCRIPT=%%f
    SQLCMD -S Localhost -E -b -V 1 -i %%f.sql -o %%f.log
    IF !ERRORLEVEL! NEQ 0 GOTO ERROR
    SET CURRENT_SCRIPT=
)
SQLCMD -S %SERVER% -E -Q "COMMIT TRANSACTION"

Because each sqlcmd execution is a separate session, it means you can't have transactions spanning those executions. You're going to want to combine your scripts into a single script for you to do what you want.

If you can properly order your scripts, the following approach in Powershell will work for you:

$scripts = Get-ChildItem "C:\temp\*.sql" |Sort-Object
$fullbatch = @()
$fullbatch += "BEGIN TRANSACTION;"

foreach($script in $scripts){
    $fullbatch += Get-Content $script
}

$fullbatch += "COMMIT TRANSACTION;"

sqlcmd -S localhost -d test -Q "$fullbatch"

Essentially, I read in each script (assuming they are ordered correctly) into a single batch variable, append a BEGIN TRANSACTION and END TRANSACTION to the batch, then execute that back as a query within my sqlcmd execution.

StackExchange Database Administrators Q#93599, answer score: 11