Archive for the ‘SQLCMD’ Category

Run / Executing SQL files as a batch using SQLCMD and XP_CMDSHELL

September 4, 2011

I had to run a set of SQL files into one of my DB. As i haven’t had a tool (like multiscript), i was checking the easy way of reading the files, reading from my local folder and executing them all in one go.

Here you go…. ūüôā

SET NOCOUNT ON

SET XACT_ABORT ON
BEGIN TRAN

DECLARE @DBServerName¬†¬† VARCHAR(100) = ‘(local)’¬†¬† — SERVER NAME
DECLARE @DBName VARCHAR(100) = ‘MyDB’¬† — DB Name
DECLARE @FolderPath¬†¬† VARCHAR(200) = ‘D:\MyFloder\DBScripts\’¬† — FOLDER PATH
DECLARE @DirCommand VARCHAR(100) = ‘dir ‘ + @FolderPath + ‘*.sql /b’

DECLARE @FileList TABLE (Files NVARCHAR(MAX))
INSERT INTO @FileList EXEC xp_cmdshell @DirCommand
–SELECT * FROM @FileList

PRINT ‘Execution starts…!’
WHILE (SELECT COUNT(Files) FROM @FileList) > 0
BEGIN
DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) Files FROM @FileList)
DECLARE @command¬† VARCHAR(500)¬† = ‘sqlcmd -S ‘ + @DBServerName + ‘ -d¬† ‘ + @DBName + ‘ -i “‘ + @FolderPath + @Filename +'”‘
–PRINT @command
EXEC xp_cmdshell  @command

PRINT ‘Executed: ‘ + @FileName
DELETE FROM @FileList WHERE Files = @FileName
END
PRINT ‘Execution ends…!’
COMMIT TRAN

Courtesy : Stack overflow.  (Above is the bit tweaked version)