Tip 41 – Creating Tables with SQL

There are several two ways to create tables using SQL commands. The CREATE TABLE statement is the most commonly used You can also create a table based on one or more tables using the SELECT … INTO syntax.

The CREATE TABLE statement specifies a table name and the columns (fields) to be added. There are also may additional options depending on whether or not you are connected to a data dictionary. When creating a table in a dictionary you can specify table and column constraints. If you are creating a Visual FoxPro 9 (VFP) table type you can specify an additional vfp-option. These include NULL, NOT NULL and NOCPTTRANS. See the examples below:

   1: // Create a table with five fields

   2: CREATE TABLE Person

   3:   ( PersonID autoinc,

   4:     LastName char(50),

   5:     FirstName char(50),

   6:     HireDate date ,

   7:     EnrollmentDate date);

   8:  

   9: // Create a dictionary bound table with constraints

  10: CREATE TABLE Department

  11:   ( DepartmentID integer PRIMARY KEY CONSTRAINT NOT NULL,

  12:     Name char(50) CONSTRAINT NOT NULL,

  13:     Budget money CONSTRAINT NOT NULL CONSTRAINT MINIMUM '0',

  14:     StartDate date CONSTRAINT NOT NULL,

  15:     Administrator integer);

The SELECT … INTO syntax creates a table based on the field specified in the field list. You can use this to create a new table based on one or more tables. You can also use any views you have defined in your dictionary to create the new table. This can be very useful for creating tables filtered based on specific criteria or creating a table with a specific order.

   1: SELECT c.CompanyName,  COUNT(i.OrderID) AS TotalOrders, 

   2:        SUM(i.SubTotal) AS TotalSales 

   3:   FROM CustomerDetails c LEFT OUTER JOIN Invoice i 

   4:   ON c.CustID = i.CustID

   5:   WHERE OrderDate BETWEEN '2009-04-01' AND '2009-06-30'

   6:   GROUP BY c.CompanyName

   7:   ORDER BY TotalSales DESC

It is important to note that a SELECT … INTO statement will always create a free table. The CREATE TABLE statement will create the table in the dictionary, if the user has the proper permissions, on a dictionary connection by default. You can create a free table by adding the AS FREE TABLE option to the end of your CREATE TABLE statement when connected to a dictionary.