Execute SQL script from command line

Question

I need to alter a database using a batch file, for a simple example, drop a table. I´m using local SQL Express (SQL Server 2008 R2) with user sa and its password.

How would the bat file be?

How can I specify in the script the password and that I use in SQL Express?

Solution 1

Take a look at the sqlcmd utility. It allows you to execute SQL from the command line.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

It's all in there in the documentation, but the syntax should look something like this:

sqlcmd -U myLogin -P myPassword -S MyServerName -d MyDatabaseName 
    -Q "DROP TABLE MyTable"

Solution 2

You can do like this

sqlcmd -S <server Name> -U sa -P sapassword -i inputquery_file_name -o outputfile_name

From your command prompt run sqlcmd /? to get all the options you can use with sqlcmd utility

Solution 3

If you use Integrated Security, you might want to know that you simply need to use -E like this:

sqlcmd -S Serverinstance -E -i import_file.sql

Solution 4

Feedback Guys, first create database example live; before execute sql file below.

sqlcmd -U SA -P yourPassword -S YourHost -d live -i live.sql

View more solutions

Comments

[RBT]

providing a file name containing all my SQL queries is much more handy than providing the SQL query as command-line arguments.

[Sakura Kinomoto]

Question are about Microsoft SQL Server, not MySQL. Check question tags.

[PAS]

I ran, sqlcmd -i filename.sql and it connected local sql server with integrated login and ran script... so looks like it has default values