SQL Script (Lesson-6)

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.

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

SELECT * FROM Tasks

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:

SELECT TaskName FROM Tasks

WHERE StatusId = "3"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:

​

​

SELECT Ta

​

​

​

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:

CREATE TABLE Status(

    StatusId int IDENTITY(1,1) NOT NULL,

    StatusName varchar(50) NOT NULL,

    DateCreated datetime NOT NULL CONSTRAINT DF_Status_DateCreated  DEFAULT (getdate()),

    CONSTRAINT PK_Status PRIMARY KEY CLUSTERED (StatusId)

)

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):

INSERT INTO Status (StatusName) VALUES ('To Do');

INSERT INTO Status (StatusName) VALUES ('In Progress');

INSERT INTO Status (StatusName) VALUES ('Done');

​

SELECT * FROM Status

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:

UPDATE Tasks

SET StatusId='1'

WHERE TaskId='1';

​

UPDATE Tasks

SET StatusId='1'

WHERE TaskId='2';

​

UPDATE Tasks

SET StatusId='2'

WHERE TaskId='3';

​

UPDATE Tasks

SET StatusId='3'

WHERE TaskId='4';

​

UPDATE Tasks

SET StatusId='3'

WHERE TaskId='5';

​

UPDATE Tasks

SET StatusId='2'

WHERE TaskId='6';

​

UPDATE Tasks

SET StatusId='1'

WHERE TaskId='7';

​

SELECT * FROM Tasks

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.

    

Note :- This is Only Knowledge Purpose and Blog Contain is my View  + Internet Search and Guideline.