HubPages»
Technology»
Computers & Software»
Computer Science & Programming

Coldfusion SQL Tips: Creating Tables, Backup, Fields

Updated on October 14, 2012

Adobe ColdFusion Anthology: Clear and Concise Concepts from the Fusion Authority

Introduction

As a developer, I have been in different Company situations and/or web projects, that its sometimes difficult to get access to SQL servers, and/or modifying existing SQL Database tables for my web applications. I have come up with some of my more often used SQL/Coldfusion code to help you from a web page, create, modify, backup, etc., SQL database tables with Coldfusion.

Create a new SQL table

The below example creates a new SQL table with several different field types.

<cfquery name="createtable" DATASOURCE="yourdatasource">
CREATE TABLE new_table_name
(
id INT IDENTITY PRIMARY KEY,
calendardate datetime,
processed_date datetime,
inv_num char(25) null,
inv_date datetime,
form_num char(50) null,
priority char(10) null,
whatqueue char(25) null,
date_received datetime
)
</cfquery>

Backup a SQL table to a new SQL table

The below example takes every record from orders_table and creates a new identical table with the data to orders_table_08242012

<cfquery name="backuptable" DATASOURCE="yourdatasource">
SELECT *
	INTO orders_table_08242012
	FROM orders_table
</cfquery>

Backup SQL Table based on a filter

The below example does a back up of all orders that have a order date less than 01/01/2012

<cfquery name="backuptable" DATASOURCE="yourdatasource">
SELECT *
	INTO orders_table_08242012
	FROM orders_table
WHERE orders_date < '01/01/2012'
</cfquery>

SQL to add new fields

The below example, adds some additional fields to an existing SQL table.

<cfquery name="addfields" DATASOURCE="yourdatasource">
ALTER TABLE ORDDER_TABLE 
ADD
contact_fname2 CHAR(50) NULL,
contact_lname2 CHAR(50) NULL,
contact_email2 CHAR(50) NULL
</cfquery>

Modify existing field in SQL table.

The below example, modifies existing fields in a SQL table.

<cfquery name="modifytable" DATASOURCE="yourdatasource">
ALTER TABLE orders_table
 ALTER COLUMN [order_amout] INT
</cfquery>

Drop or Delete a field

The below example removes a field from the SQL table.

<cfquery name="removefield" DATASOURCE="yourdatasource">
ALTER table orders_table
	DROP COLUMN Birth_Date; 
</cfquery>

Conclusion

As you can see from the examples above, you have the flexbility to perform all the SQL table operations needed from a code perspective, rather than having to be actually sitting in front of the SQL server, or remote access to the SQL server.