1. Executing a script file from SQLCMD...
i. Create a script file by typing in the following line and save it as Message.sql
print 'Welcome today''s date is: ' + (convert(varchar, getdate()))
ii. Now goto command prompt and type: SQLCMD -i Message.sql
here, -i is the switch to specify the input file name.
2. Executing series of script files (sample)
Lets create couple of .sql files and then see how to execute them in order from command prompt. Please note that i am just showing an example here :) there are better methods of doing the same which I would explain later in the series!
i) Copy paste the below script and name it as 01TableCreation.sql
Create table tblTest
(
Sno int identity,
FName varchar(20)
)
Go
ii) Copy paste the below script and name it as 02InsertRecords.sql
Insert into tblTest (Fname) values ('a')
iii) Copy paste the below two lines and name it as test.bat
sqlcmd -U sa -P hotmail -S VADIVEL -d testbed -i "C:\Vadivel\SQL
Related\Scripts\sqlcmd\01TableCreation.sql"
sqlcmd -U sa -P hotmail -S VADIVEL -d testbed -i "C:\Vadivel\SQL
Related\Scripts\sqlcmd\02InsertRecords.sql"
here,
-U is SQL User name
-P is SQL Password
-S is the SQL Server name
-d is the SQL database name
-i is the input file to execute
iv) Execute the batch file
Now goto command prompt (Start >> Run >> cmd) and execute this batch file. The batch file would have created a table and inserted a record into it. If you want to deploy some DB scripts on a remote box for which you don't have access from Mgmt Studio you can follow this batch file route. So that the ppl who are having access to that SQL box can just run this batch file (after changing the values of the different switches, if need be)
Comments