Oh my god. It's full of code!

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!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s