Coldfusion SQL Tips: Creating Tables, Backup, Fields
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>
Other ColdFusion Articles
- Adobe Coldfusion Tip For Finding Duplicate Records In SQL Database
- Coldfusion: allowing max file size when uploading
- ColdFusion: Monitor Hard Drive Space And Email Alert
- Coldfusion SQL Tips: Creating Tables, Backup, Fields
- Coldfusion Programming: Accessing a shared network drive
- Coldfusion: Encrypting and Decrypting Data
- Adobe Coldfusion Help: Query To Spreadsheet
- ColdFusion: Upload a pipe delimited file and insert into SQL table
- Coldfusion programming: How to submit muliple records at the same time that have the same field name
- How To Upload Multiple Files With ColdFusion
- ColdFusion & Javascript: How to pause a web page process
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.
Comments
No comments yet.