SQLCMD 101

Created by Wayne Pinkham

for those who might be interested

Sqlcmd is a tool for accessing Microsoft SQL Servers and running SQL queries on SQL databases. You run sqlcmd from an MSDOS command prompt. This page will introduce you to the sqlcmd and how to connect to a Microsoft SQL Server and database. You will also learn how to create a table, enter data into the table, and create queries for data using the sqlcmd application.

To get started you will need to get Microsoft's SQL Server. Click here to get the Microsoft's SQL Server 2005. Download and install the MS SQL 2005 Server. Use the Mircrosoft SQL Server Management Studio to set up you SQL server.

usage: Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f | i:[,o:]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"] [-A dedicated admin connection]
[-X[1] disable commands, startup script, enviroment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]

After viewing the syntax summary you want to get started by connecting to your SQL Server and opening our database using your userid. The database should be defined on the SQL Server and your userid should have be added to the database Users list.

In my case I created the database testing and added the userid waynesa to the database using the Microsoft SQL Server Management Studio. At the command prompt type the command shown below and press the enter key. Use your SQL Server Name for sqlservername, your userid for userid and your databasename for databasename.

sqlcmd -S sqlservername -U userid -d databasename

You will be prompted for your password. Type it in and press the enterkey. If all went well you will see the 1> prompt as shown in the example below:

C:\Documents and Settings\Wayne>sqlcmd -S corona -U waynesa -d testing
Password:
1>

You are now ready to use the sqlcmd application. The one in the prompt indicates the line number. You will type in commands an press the enter key to go to the next line. The prompt will change to a 2> and so on. You must type the command GO to execute the commands you have entered.

The following example shows the creation of the table I called MyData. I have entered the column anmes and types one per line. This helps especially when you are starting out as you can use the up and down arrows to go back and forward through commands in case you have trouble. The commas separate the columns.

1>CREATE TABLE MyData (
2>part CHAR(12),
3>number INTEGER,
4>date_ordered DATETIME,
5>date_shipped DATETIME,
6>date_received DATETIME,
7>date_payment_received DATETIME,
8>status CHAR(10),
9>CONSTRAINT part_number PRIMARY KEY(part,number))
10> GO

The table created above has a few different types of data CHAR, INTEGER (INT could be used), and DATETIME. CHAR(10) means upto 10 characters.

Now it is time to added the data to our table. In the example below I have added the values to the table separated by commas. Each of the three lines insert data into a new row on the table. Care needs to be exercised here as forgetting the apostrophe before or after the CHAR data will cause errors, as will entering to many characters. Note that I have used leading and training apostrophes on the DATETIME data.

1>INSERT INTO MyData VALUES('pin',101,'2008/03/13','2008/03/14','2008/03/23','2 008/04/01','discon')
2>INSERT INTO MyData VALUES('pin',1021,'2008/04/13','2008/04/14','2008/04/23','2008/05/01','ordered')
3>INSERT INTO MyData VALUES('pin',1031,'2008/04/03','2008/04/04','2008/04/10','2008/05/01','instock')
4>go

(1 rows affected)

No mistakes were found and now there are three rows of data in the table. To check this we can use the SELECT FROM command as shown below:

1>SELECT * FROM MyData
2>GO

part number date_ordered date_shipped date_received date_payment_received status
---------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------
pin 101 2008-03-13 00:00:00.000 2008-03-14 00:00:00.000 2008-03-23 00:00:00.000 2008-04-01 00:00:00.000 discon
pin 1021 2008-04-13 00:00:00.000 2008-04-14 00:00:00.000 2008-04-23 00:00:00.000 2008-05-01 00:00:00.000 ordered
pin 1031 2008-04-03 00:00:00.000 2008-04-04 00:00:00.000 2008-04-10 00:00:00.000 2008-05-01 00:00:00.000 instock

(3 rows affected)

The astrix was used to as a wildcard to show all the data in the table.

The following example shows how to select specific colums of data:

1>SELECT part,number,status FROM MyData
2>go

part number status
------------ ----------- ----------
pin 101 discon
pin 1021 ordered
pin 1031 instock

(3 rows affected)

The following example uses the WHERE and LIKE clauses to get the data for part, number and status for data that has the status matching any values beginning with dis.

1>SELECT part,number,status FROM MyData WHERE status LIKE 'dis%'
2>go

part number status
------------ ----------- ----------
pin 101 discon

(1 rows affected)

The following show the query above with the status removed from the colums requested.

1> SELECT part,number FROM MyData WHERE status LIKE 'dis%'
2> go

part number
------------ -----------
pin 101

(1 rows affected)

Note the data is still for item with the status discon only the staus is not included.