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 Northwind
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:
USE Northwind
SELECT * from suppliers
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:
USE Northwind
SELECT contactname, companyname, country from suppliers
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:
USE Northwind
SELECT contactname, companyname, country from suppliers
ORDER by country ASC
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:
USE Northwind
SELECT contactname, companyname, country from suppliers
WHERE country = ‘USA’
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)
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:
USE Northwind
SELECT contactname, companyname, country from suppliers
Where country = ‘Netherlands’
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:
USE Northwind
UPDATE suppliers
SET contactname = ‘Dirk Lucky’
WHERE contactname = ‘Dirk Luchte’
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.
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:
USE Northwind
INSERT INTO suppliers (contactname, companyname, country)
VALUES (‘Fred Bilko’, ‘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.
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:
USE Northwind
SELECT contactname, companyname, country from suppliers
Where companyname = ‘Magic Goods’
Result: We see ‘Magic Goods’
b) Now we go for the DELETE
USE Northwind
DELETE from suppliers
Where companyname = ‘Magic Goods’
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.
- ,comma – used to separate values
- ‘single speech marks’ valuables with spaces
- (Brackets) have different functions from quotes, they are used to mark out a series of values.
- = 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.
