SQL Tutorial - Table Manipulation in SQL

In Previous articles we've looked at the manipulation of data with our databases tables, in this article we will look at the ways in which we can manipulate out tables using SQL.

The following commands will be introduced, clicking an item below will skip directly to that section:

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE

CREATE TABLE

Although in many cases you will be able to create your tables through a GUI provided by your database supplier there may be occaisions when you want to create table programatically though SQL.  You can create you tables and columns within them fairly simply using the following syntax:

So, to programatically create the SQL Employees table used in previous articles (SQL Tutorial - The Basics - Part III) you would use the following SQL:

This has the added complexity of applying a default value to the Salary field of 0, this simply sets the value to 0 if no value is included in an INSERT INTO or SELECT INTO command.

ALTER TABLE

once you have created your table there may be times when you wish to alter the structure of it.  You can use the ALTER TABLE command along with the following keyowrds to amend the structure:

ADD - Adding a new column to the table:

CHANGE - Changing the name of a column:

MODIFY - Changing the datatype of a column:

DROP - Removing a column from the table:

DROP TABLE

If the need arises you can remove a table from a database through SQL.  This removes the data and the structure of the table.  To remove the Employees table we would use the following syntax:

TRUNCATE TABLE

If removing the table and structure is a little too much and all that is required is to delete or purge all of the data then you can use the TRUNCATE command.  To remove all of the data from the Employees table you would use the following syntax:

TRUNCATE v DELETE FROM

You could achieve the same result using the DELETE FROM command as described in 'How to Update and Delete Records' the main difference between these 2 methods are:

• Using the TRUNCATE is quicker as with DELETE FROM the records are deleted row by row but truncate clears the records all at once
• The DELETE FROM maintains the DELETE requests in the transaction log so they can be traced and rolled back easier
• The TRUNCATE resets any IDENTITY field values that have been set
• The TRUNCATE command does not adhere to Foreign Key constraints so inconsistent data could occur
• The DELETE FROM command can be used in conjunction with the WHERE command to delete large blocks, but not all, of the data in a table

Back To SQL