Insert, Update, Delete

1. Insert, Update, Delete

In this lesson we'll look at inserting, updating and deleting records

2. INSERT

Once we've created a table, we'll need to enter some data into it. One way to do this is to use an INSERT statement. This begins with the keywords INSERT INTO, followed by the table name. We then type the column names we want to insert data into, inside parenthesis. We must separate each column name with a comma. Next, we type the keyword VALUES, before opening parentheses again, and typing the individual values you want to enter, again, each separated by a comma. Then we close the parenthesis, to complete the INSERT statement.

3. INSERT SELECT

Another method we might use to populate a table is to perform an INSERT SELECT. This starts off similar to an INSERT - we use INSERT INTO followed by the table name and column names. Instead of typing VALUES, we type SELECT, then the names of the columns we want to select, and the name of the source table. We can also apply any WHERE conditions at this stage. It's always a good idea to be specific about the names of the columns we want to insert into, and the corresponding names of the columns we want to SELECT from. Also, we should take care that the source and destination column names are in the correct order.

4. UPDATE

We can amend records using an UPDATE statement. This begins with the keyword UPDATE, followed by the table name. This is followed by the SET keyword, the column we want to update, the value we want to change the record to, and, most importantly, a WHERE clause with the conditions that identify the specific rows we want to update. Don't forget the WHERE clause, otherwise, we'll update ALL the values in the column. If we want to update more than one column at a time,we simply list the additional columns and their respective new values

5. DELETE

It's very easy to DELETE in SQL Server. Be aware though, we won't get asked to confirm - there are no "are you sure?" prompts, so when we run a delete statement, it will take place immediately. The syntax is simply DELETE FROM, the table name, and the WHERE clause to specify the records for deletion. Be sure to test the WHERE clause first. Another method is to use the TRUNCATE TABLE statement. This does not accept or require a WHERE clause - it will remove all data from all columns at once.

6. Let's INSERT, UPDATE, and DELETE!

You may not have permissions to make changes to tables, but we have a safe learning environment here so let's try our hand at inserting, updating and deleting records.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.