Friday, March 6, 2015

Running SQL Scripts Through Command Line

Let me start off by saying I am mainly an SSIS kind of guy. I don't normally find myself running SQL scripts inside of management studio. Today, I was putting together some sample databases so that I had something different to destroy other than AdventureWorks. I located a dataset that consisted of hundreds of thousands of books, ratings, and locations of users that rated the books. The download consisted of three SQL scripts that created the tables and inserted the data. Easy enough. Well, when I opened the first script, I realized that the script wasn't written for MS SQL. No big deal; I just had to make some minor adjustments. When I ran the first script, I was presented with this lovely error...



I haven't had this happen before, but I have never tried to load over half a million rows of data with a SQL script either. This is when I must turn to the power of the SQL command line. Using the command line to run SQL scripts uses a lot less memory. Like most applications that have command line options, it also runs a lot faster.

First, open up a command prompt. There are lots of different parameters you can use with the command line tool. In order see a complete list, use the following command.

SQLCMD -?

Since I am doing this on my local instance, the command is rather small and straightforward. When running the command against a default instance on a local server, all you have to do is point to the target database. Also, since I have the scripts saved to a file, I have to specify the location of those files. To do this, I will use the parameters '-d' and '-i' respectively. The command prompt should look like the following screenshot.


I am pointing to the database named 'BookRankings' and running a script that is stored in a folder on my desktop. Running this command took less than a minute. After it was completed, I ran a select statement against the table just to make sure, and as you can see from the image below, everything loaded just fine. 


So remember, if you ever have to run an insanely long SQL script that SSMS just can't handle, use SQLCMD. It's fast, convenient, and the black, abysmal environment of the command prompt is kind of like working in the dark,which is where you will find me. 

0 comments:

Post a Comment