Insert, Update and Delete

Now that I have covered a vast amount of ground with the SELECT statements - I would like to improve and build on the knowledge I know when inserting, updating and deleting records.

Today's blog post will discuss the INSERT, UPDATE and DELETE statements, which will allow you to add, modify and remove data within your database. This will be incredibly useful as one of the main purposes of a database is to keep your data in a secure place where it can be easily modified.

INSERT (or INSERT INTO)

The INSERT statement allows you to add new rows into a database table. A simple INSERT statement is as follows:

Unless otherwise specified, the new row will contain no data and this will need to be considered when adding to columns that are NOT NULL and require some form of value in order to successfully committed to the database:





Note that if you see the INSERT INTO statement (like in the example below), it is only optional and means exactly the same as a normal INSERT statement:

You must always put the values after the INSERT statement within parentheses. You may need to use single quotes to surround the input values if the inputs are string or date format though this will not be necessary for integer values. You also will not need single quotes if you are using a scalar function, for example when you are putting the current date and time stamp in the column.

Identity columns should automatically be generated by SQL Server. Depending on your database schema, you may need to provide some if not all of your column details in order to make your changes and if you do not supply the correct number of required values, you will be presented with the following error when trying to run the query:

INSERT ... SELECT 

The INSERT ... SELECT statement allows you to add rows to an existing table based on rows from a different table. These do not even need to be in the same database, providing you use the database connection string at the beginning to state that is the exact database you are taking the data from, otherwise you will receive an error. An example of this is:

SELECT ... INTO 

You can also use a SELECT ... INTO statement which will allow you to insert data from a table into a brand new table (that can be either temporary or permanent). When creating temporary tables, it is good practice to place a hash symbol (#) at the beginning so this will make it easier to differentiate from your permanent tables. An example of this is shown below:

UPDATE 

Over time, you may wish to update data that already exists within the database. The UPDATE statement allows you to modify the values of multiple columns and rows within a specified table.

When creating an UPDATE statement, you will need to state:

• Where the update is going to take place,
• What is going to be modified,
• What it will be modified to.

An example of this is:

You can also use a FROM clause to UPDATE information based on data from another table such as:

DELETE 

You may wish to delete redundant data from your tables and the DELETE statement will allow you to do this. This is a very simple statement:

However, you have to be very careful when attempting to delete data which has connections to other sub-tables as you will receive an error message to state that this cannot be done. In the example below, I am trying to delete an Item Category by removing it from the ID field:

However, because there are foreign key relationships between another table, the following error message appears and the statement is cancelled and any changes have been rolled back (click on image for full sized version):

You can get around this by using a cascade delete and setting these options in the relationship tab when creating your tables. Under INSERT and UPDATE specific, you can choose to modify both the DELETE Rule and the UPDATE Rule to Cascade so it impacts all related records under that foreign key constraint:

Now when you execute the DELETE statement, all Items related to that Item Category will also be deleted. But, you may not want this to happen in all circumstances, so use cascade wisely.

TRUNCATE

The TRUNCATE statement is typically used to remove all data from a table and performs in a similar way to a DELETE statement with no WHERE clause, however there are some subtle differences that need to be taken into account before you decide which statement is best for the job. Note that this does not impact the structure of the database or its schema at all as the table will still exist until it has been dropped (and we will come onto this in a later blog post). 

An example of a TRUNCATE statement is as follows: 

Where TRUNCATE differs however is that despite the statement removing all data where specified, it will not fire any delete triggers, which makes it much faster than the DELETE statement. You may think that if this is the case, why not just use TRUNCATE all the time? Well firstly, TRUNCATE has no WHERE clause so you can only remove entire data sets from a table rather than specifically stating the exact row and exact column in certain circumstances. Also, you cannot roll back a TRUNCATE statement yet you can with DELETE, which means that if the data is deleted it is permanently gone so be careful! Another point is if you have a foreign key on the table you are trying to TRUNCATE, even if it has no related records from any other table in the database, the statement will not be committed and you will receive an error. You will need to remove these first before you attempt to TRUNCATE.