SQL 2000 Transact Statements

This section is designed for for SQL administrators who want to get started with Transact commands.

My goal in this section is to help you to write SQL scripts.  The purpose of these examples is to show you how to modify existing scripts, or to write a new script which will automate a routine task.

Typical Tasks

• Tweak Backup scripts
• Modify Permission on a database or table
• Amend DTS Packages
• Adjust Maintenance Jobs

Transact SQL v Wizards

Before you start with Transact SQL I must tell you that you could carry out many of the tasks using the built-in wizards without the need to bother with transact at all!  This reminds me of Alexander the Great and the legend of the Gordian knot.  So the story goes, many people had tried and failed to cut the knot which released a chariot; when Alexander the Great rode by he simply cut the knot with his sword - job done.  Well if you just want to get the job done, then call for the wizards.  However, if you want to learn Transact - SQL commands, then read on.

Five key verbs

USE, SELECT, UPDATE, INSERT, DELETE

Verbs are doing works.  So when you want something to happen use the correct term with its associated grammar and syntax.  Here are five verbs to master:

1) USE

USE is simple but subtle, it tells SQL which database to select. 

USE has no real purpose by itself, however, it is essential that the Transact interpreter starts in the correct database.  Let us move on to the next command verb.

2) Select

SELECT would be first verb to master.  It gives good results, teaches you the basics and what is more, you are only displaying existing information.  With SELECT there is no risk of losing  data so spend time experimenting with options like WHERE and ORDER.

To begin, open your Query Analyser (Start, Programs, Microsoft SQL Server, Query Analyser).

a) Now type this:

Press (F5) or I prefer to press CTRL + E

Result: 1: Exotic Liquids ..... and 29 more rows.

Learning Points

* is the usual wild card, meaning all fields.  from is a keyword which needs a table name, suppliers is the name of a table you wish to display.

Now that you can see the field names, you can try some variations.

b) Next type this:

Result:  Charlotte Cooper, Exotic Fruits UK

Learning Points

The field names are separated by commas, no need for a comma after the last field.  If you do make a mistake then the error messages are helpful.

Two modifiers Where and Order

Suppose you want to change the order

c) Now type this:

Learning Points

Order need by however, no more commas are needed.  I expect you have guessed that ASC means ascending, the alternative is DESC (not dec)

Next challenge, you only want a partial list

d) Now type this:

Learning Points

The crucial point is to put the 'USA' in single quotes.  This is because USA is value rather than a column (field) name.  "Double quotes" or (brackets) are no good here.  The equal sign is required here so do not worry about any other verb

Save your scripts, so that you can refer to them later. (File Save As)

See More Here

3) Update

Update is an altogether more difficult command.  There is greater potential for things to wrong.  Certainly in a real situation you would practice with SELECT before you Update or Delete.

Suppose Dirk Luchte from the Netherlands has got the wrong lastname. He should be Dirk Lucky

a) As a precaution type this:

Result: We see his name is Dirk Luchte

Learning Points

The purpose of this is to check we have the right person

b) Now let us go for the UPDATE, type this:

Result (1 Row(s) affected)

Learning Points

We already know the purpose of the 'single quotes'.  I hope you can see the crucial role of the WHERE.  Lastly note that UPDATE needs the SET command.  Again observe the equals signs.

See More Here

4) INSERT [Into]

In a transactional database, INSERT will be one of the commonest commands.  Whilst you may not be writing INSERT commands, you may on occasion need to edit an existing script.

There are numerous items that you can insert, we just want to insert a new row (record) into the suppliers table.

a) Type this to insert a record for Fred Bilko of Magic Goods, Canada:

Result: (1 Row(s) affected)

Learning Points

Firstly, Insert needs INTO  Secondly review the use of syntax; check the commas, 'single quotes' and (brackets)

Try a SELECT statement e.g. Select * from suppliers  

Note in the results of the above SELECT statement, that the last column has NULL values.  However the value for the SupplierID was created automatically thanks to the database designer.

See more here

5) Delete

Just as we showed caution with the UPDATE, so we must be careful with the DELETE verb.  Once again I would try the SELECT statement first.  Suppose we want to delete the 'Magic Goods'.

a) As a precaution type this:

Result: We see 'Magic Goods'

b) Now we go for the DELETE

Result: (1 Row(s) affected)

Learning Points

This will only work if you have INSERT Into a supplier called 'Magic Goods'.  To see how to do this see 4) above

Note that you cannot delete other suppliers because they have entries in the PRODUCTS table.  The clever database designer builds in constraints to prevent you violating the integrity of the data. 

Syntax - Four vital punctuation marks

Do not worry too much about the syntax.  There are few punctuation marks to learn and the way the analyser colour codes as you type is brilliant. 

1. ,comma - used to separate values
2. 'single speech marks' valuables with spaces
3. (Brackets) have different functions from quotes, they are used to mark out a series of values.
4. = explains itself, just the same as it has always been - equality

Error Messages

If things do go wrong then there are lots of clues in the error messages.  In particular look for the place in the statement that error message is directing you to. For example:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.

Next step

Learn some more Verbs, create your own statements.

 *

◦