CREATE TABLE

I’ve showed you how to get data out of a table, put data in, change it, and delete it.  But I haven’t showed you how to create your own table….until now.

CREATE TABLE tableName (
   columnName <datatype>
)

This statement is pretty straightforward, you have to choose the name of the table, and the names of your columns.  You also have to choose what data type you want to use for each column.  You can also list several columns rather than just one, just remember you have to separate the columns with a comma.  I haven’t talked about this before, but you have to be careful what words you choose when you get to decide what to call a column, or a table, or any other object you create.

You can’t use reserved words.

A reserved word is a word SQL uses.  For example, you can’t create a table called SELECT.  How confused would you be if someone asked you what to do with:

SELECT *
FROM SELECT

The interpreter wouldn’t know what to do with this either.  And for that reason, you need to be aware that certain words are reserved in SQL.  Basically, if you see me type it in all caps, it’s reserved.  If you have colorizing turned on in your SQL editor, and the word changes to a color different than your regular text, it’s probably reserved.  If you can search on Microsoft’s Books Online, and find it… it’s reserved.  The moral; be careful.

There is another way you can create a table.  It comes up when you already have a SELECT statement, and you want to write that data out to a “temporary” table.  I put temporary in quotes, since that phrase actually has a deeper meaning.  I’ll cover that more in another post.  For now, let me show you how to make a table from a SELECT statement.

SELECT *
INTO tableName2
FROM tableName

This will create a table using the column names and datatypes already defined in tableName.  You can then use tableName2 to do work, and when you are through with it, you can get rid of it.  I’ll show you how to delete database objects in another post too, since there are a few more concepts you’ll need to be familiar with first.

The two queries I’ve shared with you today will let you create any number of tables you wish.  You won’t be able to create two tables in the same database with the same name, but otherwise you can create any table you wish.  Just keep in mind reserved words, and you’ll be alright.  As usual, if you have any questions, send them in.  I’m here to help!