I am working with a SQL file which will update the data to a remote database. My SQL file is ok. Now the requirement is to run this SQL file from a command prompt. Is there any way to run this SQL file from command prompt or batch file?
I am using SQL Server 2012 and the SQL file is on my local machine.
Can anyone please help. Thanks in advance.
EDIT
I have tried to connect using the sqlcmd by my database connection failed
Yes you can run the SQL file from command prompt. Use sqlcmd for this.
You can find the documentation for sqlcmd here.
https://msdn.microsoft.com/en-us/library/ms162773.aspx
EDIT: You can use the below code for this. You have to place the value for the variables for your own:
sqlcmd -e -x -r -b -W -U %user% -S %server% -d %db_name% -P %pass% -i %sqlfile% -r1
Set the value of %server% as serverIP\instancename
EDIT 2: Full script should be close to this one:
Set scriptfile=InsertDB.sql
Set dbserver=
set /p dbserver=Database Server :
Set dbname=
set /p dbname=Database Name :
Set usrname=
set /p usrname=Database Username :
Set passw=
set /p passw=Database Password:
set scriptPath=%~dp0
sqlcmd -e -x -r -b -s"| ", -W -U %usrname% -S %dbserver% -d %dbname% -P %passw% -i "%scriptPath%%scriptfile%" -r1
Pause&Exit
