Mapping DataType Keywords When Using SQL DDL

In my July 2000 CFDJ article, Reconfiguring Remote Databases via SQL (available online from my site under "articles" at systemanage.com), I talked about how to use SQL DDL statements like CREATE TABLE and ALTER table to perform manipulation of your table, column, index, and constraint (primary/foreign key) definitions.

One of the challenges in using such commands, for instance when manipulating an Access database this way, is mapping the datatypes names for a column you might want to create. For instance, if in Access you refer to a column as being a "yes/no" datatype, that won't work in a CFQUERY doing a CREATE TABLE, for instance.

Indeed, keeping these mappings of Access to ODBC terminology for datatypes can be challenging.

I address this and some related issues in the article. I even offer a table there that shows these mappings such as yesno/boolean/bit/logical would equate to that yes/no. There are a couple of links offered in that article which are now (2 years later) no longer valid. See the comments at the bottom of the front page of the online article for some updates to those.

Also, I found another page recently that has some of this same info, and still some more other useful tidbits:

http://admin.nj.devry.edu/~kjudge/accessddl.htm#Comparison

It also offers several examples of how to perform SQL DDL to do all sorts of things, such as create/alter tables indexes, indexes, and constraints (such as primary and foreign keys).

Nifty stuff, and something that a lot of CFers never learn about. It can be useful for managing a remote DB especially, versus downloading the file to make the changes and then uploading it back. I discuss all that in the article.