Using SQLCMD to execute script files easily in different environments
Step 1: Lets create few dummy script files for demo purpose.File1: 01TableCreation.sqlCreate table tblTest(Sno int identity,FName varchar(20))GoFile2: 02InsertRecords.sqlset nocount onInsert into tblTest (Fname) values ('alpha')Insert into tblTest (Fname) values ('beta')File3: 03StoredProcedures.sqlCreate proc usp_GetAllTblTestasSelect sno, fname from tblTestgo
Step 2: Create a batch file and call these .sql files in order.File4: DBInstallationScripts.batsqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\01TableCreation.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\02InsertRecords.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\03StoredProcedures.sql"
Step 3: Execute the batch fileFrom the command prompt (Start >> Run >> Cmd) do the following:c:> DBInstallationScripts DBUserName DBPassword DBServerName DatabasenamePlease note we are passing the database username, password, servername and the database to which we need to connect from the batch file. That way it would replace %1, %2, %3 and %4 in the batch file while executing.
Same set of scripts can be executed in different environments like 'Development', 'Testing', 'Production' etc., with ease.
