Coldfusion SQL Tips: Creating Tables, Backup, Fields
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
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.
More by this Author
You don't always have to tile, carpet, or hardwood your floors. The new thing is to just stain the concrete that's already there. It's a lot of work, but if done properly, your home will look gorgeous. If you're...
Until this year, the only time that I've had mice or rats in the house were when my daughter had pet mice and rats, and one of those times we did have one loose in the house. (Science fair project got loose.) Anyway,...
You may refer to a truss as the rafter, but it's basically the skeleton of the roof, carrying the weight of the frame and supporting the walls of the building. Trusses are very important to preventing the walls from...