CF_GenInserts - Way Cool Custom Tag

Posted At : September 13, 2007 1:59 PM | Posted By : Phillip Gagnon
Related Categories: ColdFusion,MSSQL

So the other day, I was talking with a friend of mine and we came up with a problem. We were both working on a project, and we ended up with disparate sets of data. The database schemas were the same, but I had inserted some data that he needed. I could have just sent him a SQL backup file, but he had data in his database that I did not have. So the only solution was to create INSERT statements for all the data that I had inserted, and for him to do the same. Obviously, this is a tremendous waste of time. So we came up with "CF_GenInserts"

Here is the gist of how it works, you run a query, and pass the result set (Query Object) to the tag, as well as the name of the table to insert the data to. The tag then reads all the data, generates an insert statement for each record, and writes it to a .sql file.

Also, you can specify a path for the file to be written to. In this example, I am selecting a bunch of data from the Northwind database.

<cfquery datasource="northwind" name="stuff">
   SELECT EMPLOYEEID
,LASTNAME
,FIRSTNAME
,TITLE
,TITLEOFCOURTESY
,BIRTHDATE
,HIREDATE
,ADDRESS
,CITY
,REGION
,POSTALCODE
,COUNTRY
,HOMEPHONE
,EXTENSION
,NOTES
,REPORTSTO
   FROM employees
</cfquery>


<cf_genInserts tableName="employees" theQuery="#stuff#" pathForFile="#ExpandPath('.')#">

Done!!

And that is it! The result is a file called "employees.sql" at the same location as this page (wherever that may be on the server). By default, the file is created wherever the custom tag is.

The tag can be downloaded here and is released under the GNU public license. Basically, use the software wherever you want, but please don't modify it. If you would like to see some changes or modifications, please drop me a line, as I would love to make the changes for you.