Thus far, we’ve only worked with the actual data inside existing tables. But now we’re going to learn about adding new tables to our database. Later one we’ll get into the much more complicated steps of designing a good database. For today’s lesson, we’ll just keep it simple. You’ll learn how to get a new table added to your database using the CREATE SQL statement.
The word schema is used to represent the structure of our database. The schema is all of the tables and things that tie them together or build off of them. We still have lots to learn about the different kinds of objects available in SQL server. Starting today, we’ll find out how the schema is modified.
The CREATE statement is the key to adding new things to our system. You can use it to add tables, stored procedures, views and even new databases. We’ll look at all of this later on. Today we’re going to focus on adding new tables. Let’s jump right in!
CREATE TABLE [name] ([columnname1 datatype1, columnname2, datatype2])
CREATE TABLE TimeZones (Name varchar(50), GMTOffset int)
This statement does not add or change any data. Instead, it creates a new table. After running the example statement run the following:
SELECT * FROM TimeZones
You’ll see that a table now exists but has nothing in it. This table is now a regular part of your database just like any other tables we’ve played with thus far. The syntax is pretty self-explanatory. Now that we know what variables are, we can use them to define a table. Let’s say we forgot to add a column to our TimeZone table. We might want to have the three-letter abbreviation to go with the other data. This is when we’d need to use the ALTER statement to modify an existing table.
The ALTER statement actually has a variety of uses for modifying schema data. Right now, we’re just going to focus on how it relates to tables and adding columns.
ALTER TABLE [tablename] ADD ([columnname1 datatype1, columnname2 datatype2])
ALTER TABLE TimeZones ADD (Abbrev varchar(3))
Once this completes. Run another select. You’ll see that your new column now exists. The new column is also a permanent part of the database. However, sometimes we’ll need to remove things as well.
The ALTER command also serves double-duty to remove things from the schema. Let’s say we changed our mind about the name or data type for our Abbreviation column. There are ways to alter it without removing it. But if the table is already full of data, we’re going to run into some issues. For that reason we’ll remove the column.
ALTER TABLE [tablename] DROP COLUMN [Columnname]
ALTER TABLE TimeZones DROP COLUMN Abbrev
This command will remove the column we had just added from the table. This is a permanent change and any data that exists in this column will be gone. For this reason, use it with caution!
At this point, you already know everything you’d need to make a new column and move the data into it. This can be by using a combination of ALTER and UPDATE statements. First, we create the new column. Then, we use update to move the data from the old column to the new one. Lastly, we delete the original column. Let’s say we want to make the abbreviation column have 10 characters but we already have rows filled with data.
First, we create our new column:
ALTER TABLE TimeZones ADD (Abbrev10 varchar(10))
Then, we copy what was already in Abbrev over to Abbrev10.
SET Abbrev10 = Abbrev
This updates all the records with a direct copy from one column to the other. Now we can get rid of our old column.
That’s it! We just made a new column and moved the existing data over to it. There is one more thing we’re going to do in today’s lesson.
At some point, we might want to erase an entire table from our schema. It’s also very easy to do. However, it is permanent. The data in the table will be lost. Think wisely before removing a table.
DROP TABLE [tablename]
DROP TABLE TimeZones
Running this command will remove the existing table from the database. Remember that all of these commands have a big impact on your data. Use them wisely so you won’t lose something important.
Remember when we talked about temporary tables? This is what we’ll get into next. Everything we just learned will be very important with working with temporary tables. We’ll see some of the amazing things we can do with temporary tables in SQL server.
Tainyan staff who teach SQL.