564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Using T-SQL to create tables programmatically Part three of a four-part series of blogs |
---|
SQL doesn’t just select data; you can also use it to create tables and link them together with relationships. This blog shows you how!
This blog is part of a much longer tutorial on programming in SQL. Alternatively, have a look at our classroom-based training courses in SQL.
|
If you want complicated syntax structures, you can get these from the Microsoft website; this blog concentrates on practical examples.
To create a simple table, just list out all the columns that you want to create, giving each a name and a data type:
-- create the table, giving it a name
CREATE TABLE tblAuthor (
-- list all the fields (name and type)
FirstName as varchar(100),
LastName as varchar(100)
)
You can see a separate blog on the possible data types that you can use, but here are some of the main ones:
What you're storing | Use | Notes |
---|---|---|
Any string of text | varchar(n) | A string of text with up to n characters. |
Any whole number | int | Any whole number (eg 42). |
Any other number | float | Any other type of number (eg 3.14). |
Any date | date | Any date |
Any yes/no value | bit | Either 0 (False) or 1 (True). |
You can set which column in a table is the primary key using the PRIMARY KEY key words, and make this automatically number rows using IDENTITY:
CREATE TABLE tblAuthor (
-- AuthorId field is primary key
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100)
)
-- type in a couple of authors
INSERT INTO tblAuthor(
FirstName, LastName
) VALUES (
'Barbara','Kingsolver'
)
INSERT INTO tblAuthor(
FirstName, LastName
) VALUES (
'Ian','Banks'
)
-- show the results
SELECT * FROM tblAuthor
What the above code would do is to create and show the following rows:
The rows returned by the above SQL.
Notice that because we've used the IDENTITY keyword, there is no need to specify the AuthorId field when inserting records - its value will be generated automatically, beginning at 1 and incrementing by 1 for each new row.
You can specify for any column that it should take a default value if not specifically set. Here we set the date added column to default to today's date:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
-- the date when the author was added
-- will default to today's date
DateAdded datetime DEFAULT GetDate()
)
If you insert rows into the table, you can choose whether or not to set a value for the DateAdded column:
-- first author defaults to today's date
INSERT INTO tblAuthor(
FirstName, LastName
) VALUES (
'Barbara','Kingsolver'
)
-- for next author, we'll set date explicitly
INSERT INTO tblAuthor(
FirstName, LastName, DateAdded
) VALUES (
'Ian','Banks', '01/01/2012'
)
-- show the results
SELECT * FROM tblAuthor
The results of running this query will look like this (assuming "today" is 05/10/2012):
The date has been explicitly set for the second author.
In our simple authors table, it's likely that we'll want to ensure the last name is always filled in. You can do this by using the NOT NULL keywords:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
-- make sure last name is filled in
LastName varchar(100) NOT NULL,
DateAdded datetime DEFAULT GetDate()
)
If you then insert a row, you'll get an error if you don't specify the last name, but the first name can still be null:
-- first author defaults to today's date
INSERT INTO tblAuthor(
LastName
) VALUES (
'Kingsolver'
)
-- show the results
SELECT * FROM tblAuthor
Thus the SQL above would create one row, with the first name appearing as NULL:
The results of running the SQL above.
Sometimes you want to ensure that a column in a table will only accept certain values (for our example, we might want to add a rating, which must be between 0 and 10). You can do this by adding a constraint to the table.
To show how this works, let's first create a table containing a Rating field:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
-- rating must be an integer
Rating int
)
Now we'll add in an author with a rating outside the permissible bounds:
-- add in an author
INSERT INTO tblAuthor(
FirstName,LastName, Rating
) VALUES (
'Barbara', 'Kingsolver', 11
)
To add a constraint, we have to alter the definition of the table:
-- now check rating lies between 0 and 10
ALTER TABLE tblAuthor WITH NOCHECK
ADD CONSTRAINT tblAuthorCheckRating
CHECK (Rating>=0 and Rating <>
A couple of notes about this:
If we try adding another author with rating 11 now ...
-- next time try adding rating of 11 ...
INSERT INTO tblAuthor(
FirstName,LastName, Rating
) VALUES (
'Ian', 'Banks', 11
)
-- show the results
SELECT * FROM tblAuthor
... we'll generate an error, and the row won't be added:
The error you'll see if you run the SQL above.
You can index a column to speed up sorting and searching by it. For our authors table, it's likely that we'll frequently want to sort authors by their last name, so we'll index this field:
CREATE TABLE tblAuthor (
AuthorId int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
)
-- index the LastName field
CREATE INDEX ix_tblAuthor_LastName
ON tblAuthor(LastName)
The index name must be unique over all tables, which is why we've used the naming convention ix_TableName_ColumnName.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.