TSQL – introduction – Working with data (4 of 4)

At this time you should be able to select data and working with objects, and now we will very quickly look at three important statements. They are called DML statements (Data Manipulation Language):

  1. Insert
  2. Update
  3. Delete

First we create a new database and a table:

use master
go

create database tsqldemo
go

use tsqldemo
go

create table    firstTable    (ID        int        identity    primary key
                             , name    varchar(200)
                             , birthPlace    varchar(200)
                            )

If that works and you get “Command(s) completed successfully), then we can continue with datamanipulation.

INSERT

Insert is very complex, and we will only look at the most simple form: Inserting simple values.

The syntax is

Insert into firsttable (name, birthPlace) values ('Soren', 'Vejle')

The first () where it says Name and birthplace, tells the system, that you want to insert values into the columns name and birthplace. We don’t want to insert into ID column, because it is an Identity column and SQL server takes care of that for us.

then we have the word Values and then the second (), where we have added the values (remember: text is put into single quotes ‘).

It’s so easy and you can try to write: Select * from firsttable. You should have one row.

UPDATE

Update has another syntax, so if I want to update firsttable, I can write:

Update firsttable
set name='Pernille'

It’s quite easy. You can also add a where clause:

Update firsttable
set name=’Soren’
Where name=’Pernille’

then it is only rows that meets the where clause that are updated.

DELETE

Delete has the following syntax:

Delete firsttable
where name='Soren'

ONE IMPORTANT NOTE ABOUT DELETE!!!!!!!! Always write a delete as a select clause, and then change Select * from xxxx to Delete XXXX. What i mean is. If I wan’t to delete all entries from my table where name = ‘Soren’, then I would write:

Select *
from firsttable
where name='Soren'

and then change it to:

Delete firsttable
where name='Soren'

That’s it. You now know the basics of TSQL. You have a lot to learn, but you have a good basis to move on. Use books online and Google to find more information or just write me on this blog, and I will try to help you.

<<– Back to step 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s