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!

  1. Creating Tables in SQL
  2. Creating Databases using SQL
  3. Creating a Table Programmatically in SQL (this blog)
  4. Linking Tables (Foreign Keys and Relationships)

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.

Posted by Andy Brown on 05 October 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a Table Programmatically in SQL

If you want complicated syntax structures, you can get these from the Microsoft website; this blog concentrates on practical examples.

Creating Simple Tables

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).

Setting a Primary Key

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:

Table containing 2 authors

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.

Setting Default Values for Columns

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):

Two authors in a table

The date has been explicitly set for the second author.

 

Preventing Null Values for Columns

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:

One author with null first name

The results of running the SQL above.

 

Adding Constraints to a Column

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:

  1. You have to call the constraint something.  Here we've called it tblAuthorCheckRating to ensure that this name won't be used elsewhere in the database.
  2. We need the words WITH NOCHECK to ensure that the constraint isn't applied to existing data (which would fail the check).

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:

Check constraint error message

The error you'll see if you run the SQL above.

Indexing Columns

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.

This blog has 0 threads Add post