SQL Server 2014 - SQL Scripts

• Adding Data
• Query Designer

A discussion on writing SQL scripts and some quick examples.

In the previous lesson, we added data to our database table using the "Edit Top 200 Rows" option. In this lesson, we will look at how to write SQL scripts to update and run queries against our database.

SQL scripts can be used to insert data, read data, update data, and delete data. They can also be used to create database objects such as tables, views, stored procedures, and they can even be used to create whole databases themselves - complete with tables, data, users, etc.

Transact-SQL

SQL Server supports Transact-SQL as a scripting language. Transact-SQL is based on SQL (Structured Query Language), which is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it. Whenever I refer to a SQL script here, I mean a Transact-SQL script.

SQL Statements

SQL scripts are usually made up of one or more "statements". Each statement tells SQL Server what to do.

SQL scripts can contain many statements. For example, a SQL script could contain a statement to CREATE a table, another statement to INSERT data into that table, and another statement to SELECT all the data that was just inserted into the table. In fact, SQL has specific statements that are used for those exact three things: the CREATE statement, the INSERT statement, and the SELECT statement.

Generally speaking, your statements will begin with the word of the task that you're trying to perform. If you're trying to create a database object, your statement will start with CREATE. I say "generally speaking" because, there are some other bits and pieces that you can throw in to a SQL statement that may come prior to your statement.

Example SQL Statement

Probably the easiest SQL statement to learn is the SELECT statement. Here's an example of a SELECT statement at its simplest:

The above statement selects all columns from the Tasks table. The asterisk (*) means "all columns". The above statement could be modified slightly to only return a particular column, and we could add a WHERE clause to filter the data to only those records that we're interested in:

The above statement selects the TaskName column from the Task table, but it only returns those that have a StatusId of 3. The value of 3 could mean "To Do" or "Done" or whatever it is we want it to mean. In our TaskTracker database, we'll be creating a new table called Status where we'll specify what "3" means (and "1" and "2" of course). These numbers are simply the values in the StatusId field (which we specified as an Identity column - an autonumber). Each of those numbers will have a corresponding value in the StatusName field which will tell us what status it actually is.

Then, once we've created our Status table, we could modify the above SQL statement to include the Status table so that we can write WHERE StatusName = "To Do" instead of trying to remember what number "To Do" is.

But we need to create our Status table first...

Creating Tables with a SQL Script

Below is a SQL script that will another table on our database - a Status table. If you aren't familiar with SQL it might look a bit weird. But I'm sure you'll have some idea of what it does when you see the names of the columns and their corresponding data types. In this script, we aren't allowing any NULL fields (hence the NOT NULL next to each column). We're also creating a primary key for the StatusId field, and we're setting a default value to the DateCreated field to (getdate()).

To run this script, do the following:

1. Copy the script to your clipboard
2. In the SQL Server Management Studio, click the New Query button on the toolbar:
3. Paste the script into the query window
4. Click the ! Execute button on the toolbar:

Once the script has run, you should see a message that reads Command(s) completed successfully.

Here's the script:

Keyboard Shortcut

Add Data via SQL Script

You can also add data via a SQL script. The script will use the INSERT statement to insert data into the tables that you specify. You can have the script insert data into all columns in the table, or just those that you specify.

The following script inserts data into the Status table, and then selects that data (so we can see that it went in). Run this script the same way you did with the above script):

And here's what that looks like:

As you can see, the results of the query are displayed in the bottom pane.

Cross Reference Data

Now that we've got our Status table, let's add a value to the StatusId field of the Tasks table (remember we left that field blank for all records because we didn't yet have the Status table).

So the value we add is going to link the Tasks table to the Status table. Each record in the Tasks table will now have a StatusId which will be either 1, 2 or 3 (to match a value in the StatusId field of the Status table).

To do this, we'll need to use an UPDATE statement (because we're updating the records rather than inserting new ones).

So without further ado, let's run the following script:

And you should now see your Tasks table, complete with its StatusId field populated for all records. Something like this:

Combining Scripts

Actually, we could just have easily combined all of the above scripts and run them as one. I only kept them separate in order to make it easier to understand which part does what.

If you'd like to learn more about creating SQL statements, check out my SQL tutorial.

Case Sensitivity

SQL is case insensitive. So the following statements all mean the same thing:

• SELECT * FROM TASKS
• SELECT * FROM Tasks
• Select * From Tasks
• select * from tasks

Database Administration

Most of the database administration tasks (such as creating users, backups, etc) that can be performed in SSMS via the graphical user interface can be performed programatically via SQL scripts. This tutorial concentrates on using the graphical user interface, mainly because it's usually a lot easier for new users to get their head around. Once you become more familiar with SQL Server, you may find yourself using SQL scripts to perform many of the tasks that you started out doing via the graphical user interface.

Next we'll look at the query designer.

• Adding Data
• Query Designer