SfC Home > ColdFusion >
Explanation of how to input data from an existing database table into a new table with ColdFusion. Also refer to Macromedia, CFML, record, row, field, column, CFLOOP, SQL insert function, Ron Kurtus, School for Champions. Copyright © Restrictions
Populating a New Database Table from and Old Table with ColdFusion
by Ron Kurtus (12 February 2002)
Supposed you have created a new database table, and you want to populate it with data from another table. You can use the Macromedia ColdFusion <CFLOOP> tag and the SQL INSERT function to perform that task.
Steps to take are:
- Create new table
- Create CF page with Query, Loop and Insert
- Test application
This lesson will answer those questions. There is a mini-quiz near the end of the lesson.
Create new table
First, you need to create the new table in your database and enter the names, types and sizes of its columns or fields. The names do not have to be the same as the fields in the first database, but the types and sizes should be.
Create CF page
Then, create a ColdFusion page where you query, loop and insert your data.
Query old table
Query the old table for its data. This table may be in the same database or in a different one.
<CFQUERY NAME="first" DATASOURCE="xxx">
SELECT *
FROM TableName
</CFQUERY>
Loop and insert
Loop over this query and insert the records or rows into the new table.
<CFLOOP Query="first">
<CFQUERY NAME="second" DATASOURCE="yyy">
INSERT INTO New_TableName(
FirstColumn,
SecondColumn,
ThirdColumn,
etc.)
VALUES (
'#FirstCol#',
#SecondColumn#,
'#ThirdCol#',
etc.)</CFLOOP>
Remember to add the single quotation marks ( ' ) for the text data items. Also, note that the names from the first table do not necessarily have be the same as what you enter in the second table.
Test application
Upload the page and run the application. You may get error message for data mismatches or such. Make the appropriate adjustments.
You can also use this method to add or update data in you new table from the original.
Summary
To populate new database table with data from another table, you can use the <CFLOOP> tag and the SQL INSERT function to perform that task.
Help others succeed
Resources
The following resources provide information on this subject.
Websites
Books
Mini-quiz to check your understanding
1. What is ColdFusion Studio?
2. What is needed to display ColdFusion pages on the Web?
3. What is a major application of ColdFusion?
If you got all three correct, you are on your way to becoming a Champion in ColdFusion Development. If you had problems, you had better look over the material again.
What do you think?
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. We will try to get back to you as soon as possible.
Share link
Feel free to establish a link from your website to pages in this site.
Or use our form to send this link to yourself or a friend.
Students and researchers
The Web address of this page is
www.school-for-champions.com/coldfusion/new_table_from_old.htm. Please
include it as a reference in your report, document, or thesis.
Where can you go from here?
Populating a New Database Table from an Old Table