Oh my god. It's full of code!

ColdFusion

Simple Coldfusion MSSQL Data Export/Import

Hey all,
this is just quick script I threw together for moving data from one MSSQL datasource to another. I am currently using this to help migrate a client website from my server to it’s permanent home. It’s a bit slow, so not useful for tables with more than a few hundred rows, but it’s great for the application I am using it for. Maybe it’ll help someone, maybe not. Either way, couldn’t hurt to post it.

<cfsetting requesttimeout="6000">

<cfset SourceDSN = "DS">
<cfset DestinationDSN = "PsaSmart">
<cfset tableName = "Search">

<cfoutput>

<cfquery name="GetSource" datasource="#SourceDSN#">
    Select * from psasmart.dbo.#tableName#
</cfquery>

<cfquery name="ClearExistingRecords" datasource="#DestinationDSN#">
    delete from #tableName#
</cfquery>

<cfquery name="AllowIdInsert" datasource="#DestinationDSN#">
    set identity_insert #tableName# on
</cfquery>

<cfset rowCounter = 0>

    <cfloop query="GetSource">
        <cftry>
            <cfset rowCounter = rowCounter + 1>
            
            <cfset form = structnew()>
            <cfloop list="#GetSource.columnList#" index="colName">
                <cfset form[colname] = getSource[colname][rowCounter]>
            </cfloop>
            
            
            <cfinsert datasource="#DestinationDSN#" tablename="#tableName#"> 
            Row #rowCounter# - ID #id# Inserted<br />
            
            <cfcatch type="any">
                Row #rowCounter# - ID #id# Failed #cfcatch.message# #cfcatch.detail#<br />
            </cfcatch>
        </cftry>    
        <cfflush>
    </cfloop>
</cfoutput>

<cfquery name="DisAllowIdInsert" datasource="#DestinationDSN#">
    set identity_insert #tableName# off
</cfquery>

Done!



Turn a query into an excel spreadsheet

Hey everyone. Here is a handy chunk of code I use to turn any query into a download-able excel spreadsheet. I like it pretty well so I figured I’d share.

    <cfsavecontent variable="report">

        <table border="0">
            <tr valign="bottom">
                <cfloop list="#QUERY.columnList#" index="columnName">
                    <th align="center">#columnName#</th>
                </cfloop>
            </tr>    
            
            
            <cfloop from="1" to="#QUERY.recordCount#" index="i">
                <tr valign="top">
                    <cfloop list="#QUERY.columnList#" index="columnName">
                         <td align="left" class="report">
                            <cftry>
                            #QUERY[columnName][i]#
                                <cfcatch type="any">
                                    NULL        
                                </cfcatch>
                            
                            </cftry>                                    
                         </td> 
                    </cfloop>
                </tr>
            </cfloop>
        </table>
    </cfsavecontent>
    
    <cfset Reportname = "Your Data.xls">
    
    <cffile action="write" file="C:\Website\Reports\#Reportname#" output="#report#" nameconflict="overwrite">
    
    <a href="http://YourPage/reports/#reportname#">Download Data</a>