AKH Online, Inc
Accomplished Internauts
Oracle to SQL Server via ColdFusion
A warning: this is an archive of a script to clone an Oracle database table over to a SQL Server database. It's really just a point of departure for further use by a ColdFusion developer, and was designed for rapid duplication of relatively small tables with simple values (ie no blobs!). It's never been used on more than 500 records at a time.
The code is self-referencing: Each page ("mode") is a form that calls back to the same page again. The pre-display code is ordered descending with the last mode at the top (so any failure will cause a drop to the previous mode below), while the html display is ordered in natural ascending modes.
It's posted here for me (no warranty, no refund, no support), but if anyone else can use it... great! More notes to follow after the source code below.
Here is the whole thing:
<!--- LIGHTWEIGHT "ORACLE TO MS-SQLSERVER" CLONER --->
<!--- ALAN K HOLDEN 20120201 --->
<!--- UNIVERSALLY REQUIRED PARAMS - START --->
<!--- used for validation messages --->
<cfset pageMessage = "">
<cfparam name="mode" default="start" />
<cfparam name="dsn" default="" />
<cfparam name="tableName" default="" />
<cfparam name="colNamesList" default="" />
<cfparam name="colNullsList" default="" />
<cfparam name="colTypesList" default="" />
<cfparam name="colLenList" default="" />
<cfparam name="colTypesNewList" default="" />
<cfparam name="colIdentityList" default="" />
<cfparam name="sqlScript" default="">
<cfparam name="isRunScript" default="0">
<cfparam name="dsnNew" default="" />
<!--- UNIVERSALLY REQUIRED PARAMS - END --->
<!--- TEMPLATE (PRE-HTML)- START --->
<!---
Here, process modes are in reverse order, so if any mode fails validation
then control can revert to the earlier form mode for user correction.
Below, the actual display form modes are in the same order as seen by the user.
--->
<cfif mode eq "migrate">
<!--- this last mode does the actual data migration from old to new table --->
<cfset success = 1>
<cfif success>
<!--- get columns in this table --->
<cfquery name="getRecords" datasource="#dsn#">
SELECT #colNamesList# FROM #tableName#
</cfquery>
<cfloop query="getRecords">
<!--- determine which columns in this row to actually insert --->
<cfset insertColNamesList = colNamesList/>
<cfset insertColTypesList = colTypesNewList/>
<cfloop list="#colNamesList#" index="colName">
<cfset colNo1 = listFind(colNamesList,colName)>
<cfset colValue = getRecords[colName] />
<cfif (listGetAt(colIdentityList,colNo1))
OR (listGetAt(colNullsList,colNo1) eq 'Y' AND NOT len(colValue))
OR (isNull(colValue))
OR (listGetAt(colTypesNewList,colNo1) eq "datetime" and NOT isValid('date',colValue))
>
<cfset insertColTypesList = listDeleteAt(insertColTypesList,listFind(insertColNamesList,colName)) />
<cfset insertColNamesList = listDeleteAt(insertColNamesList,listFind(insertColNamesList,colName)) />
</cfif>
</cfloop>
<!--- issue the actual SQL command to insert the columns determined above --->
<cfquery name="putRecord" datasource="#dsnNew#">
INSERT INTO #tableName#
(#insertColNamesList#)
VALUES
(
<cfloop from="1" to="#ListLen(insertColNamesList)#" index="colNo2">
<cfset colValue = getRecords[listGetAt(insertColNamesList,colNo2)] />
<cfif colNo2 gt 1>,</cfif><cfswitch expression="#listGetAt(insertColTypesList,colNo2)#">
<!--- use type to set the correct format to insert the value (determined in 'columns' mode below) --->
<cfcase value="varchar">'#colValue#'</cfcase>
<cfcase value="datetime">#createODBCDateTime(colValue)#</cfcase>
<!--- add more cases as needed --->
<cfdefaultcase>#colValue#</cfdefaultcase>
</cfswitch>
</cfloop>
)
</cfquery>
</cfloop>
</cfif>
<cfif NOT success>
<cfset mode = "start">
</cfif>
</cfif>
<cfif mode eq "create">
<!--- this mode runs the SQL table 'CREATE' script that was composed below --->
<cfset success = 1>
<cfif findNoCase('DROP',sqlScript)>
<cfset pageMessage = "DROP is not allowed in your script." />
<cfset success = 0 />
</cfif>
<cfif findNoCase('GRANT',sqlScript)>
<cfset pageMessage = "GRANT is not allowed in your script." />
<cfset success = 0 />
</cfif>
<cfif ucase(dsnNew) eq ucase(dsn)>
<cfset pageMessage = "Cant run this on the old Oracle datasource." />
<cfset success = 0 />
</cfif>
<cfif not findNoCase('IDENTITY',sqlScript)>
<cfset colIdentityList = replace(colIdentityList,'1','0','all') />
</cfif>
<cfif success>
<cfif val(isRunScript)>
<cfquery name="runCreate" datasource="#dsnNew#">
#sqlScript#
</cfquery>
</cfif>
<cfquery name="getCount" datasource="#dsn#">
SELECT COUNT(0) as Records
FROM #tableName#
</cfquery>
</cfif>
<cfif NOT success>
<cfset mode = "columns">
</cfif>
</cfif>
<cfif mode eq "columns">
<!--- this mode parses the old tables columns to write the SQL CREATE script to make the new table --->
<cfset success = 1>
<cfif success>
<cfquery name="getColumns" datasource="#dsn#">
SELECT
column_name,
nullable,
data_type,
data_length
FROM user_tab_columns
WHERE table_name='#tableName#'
</cfquery>
<cfset colNamesList = valueList(getColumns.column_name) />
<cfset colNullsList = valueList(getColumns.nullable) />
<cfset colTypesOrigList = valueList(getColumns.data_type) />
<cfset colLenList = valueList(getColumns.data_length) />
<cfset colTypesNewList = '' />
<cfset colIdentityList = '' />
<cfset colShowLenList = '' />
<cfloop list="#colTypesOrigList#" index="origType">
<!--- look at the old Oracle type to guess/set the new MS SQL type (add more cases as needed) --->
<cfswitch expression="#ucase(left(origType,7))#">
<cfcase value="VARCHAR">
<cfset newType = "varchar" />
</cfcase>
<cfcase value="DATE">
<cfset newType = "datetime" />
</cfcase>
<cfcase value="NUMBER">
<cfset newType = "int" />
</cfcase>
<!--- add more cases as needed --->
<cfdefaultcase>
<cfset newType = "--- UNKNOWN ---" />
</cfdefaultcase>
</cfswitch>
<cfset colTypesNewList = listAppend(colTypesNewList,newType) />
<cfif newType eq "int" AND NOT listFind(colIdentityList,1)>
<cfset colIdentityList = listAppend(colIdentityList,1) />
<cfelse>
<cfset colIdentityList = listAppend(colIdentityList,0) />
</cfif>
<cfif newType eq "varchar" >
<cfset colShowLenList = listAppend(colShowLenList,1) />
<cfelse>
<cfset colShowLenList = listAppend(colShowLenList,0) />
</cfif>
</cfloop>
<cfquery name="getCount" datasource="#dsn#">
SELECT COUNT(0) as Records
FROM #tableName#
</cfquery>
</cfif>
<cfif NOT success>
<cfset mode = "tables">
</cfif>
</cfif>
<cfif mode eq "tables">
<!--- this mode fetches all the tables in the provided dsn. so user can pick one --->
<cfset success = 1>
<cfif success>
<cfquery name="getTables" datasource="#dsn#">
select table_name from all_tables
order by table_name
</cfquery>
</cfif>
<cfif NOT success>
<cfset mode = "start">
</cfif>
</cfif>
<!--- TEMPLATE (PRE-HTML) - END --->
<!--- TEMPLATE HTML- START --->
<cfoutput>
<cfif len(pageMessage)><p style="color:red;font-size:smaller;font-weight:bolder;">#pageMessage#</p></cfif>
<cfif mode eq "start">
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p>Oracle datasource / database<input type="text" name="dsn" value="#dsn#"></p>
<p><input type="Submit" value="Next"></p>
<input type="Hidden" name="mode" value="tables">
</form>
</cfif>
<cfif mode eq "tables">
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p>Select table
<select name="tableName" size="28">
<cfloop query="getTables">
<option value="#getTables.table_name#">#getTables.table_name#</option>
</cfloop>
</select>
</p>
<p><input type="Submit" value="Next"></p>
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="mode" value="columns">
</form>
</cfif>
<cfif mode eq "columns">
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p>Records: #getCount.Records#</p>
<p>
<textarea name="sqlScript" cols="60" rows="30">CREATE TABLE [dbo].[#tableName#](
<cfloop from="1" to="#listLen(colNamesList)#" index="colNo3">
<cfif colNo3 gt 1>
,</cfif>[#listGetAt(colNamesList,colNo3)#] [#listGetAt(colTypesNewList,colNo3)#] <cfif listGetAt(colIdentityList,colNo3)>IDENTITY(1,1)</cfif> <cfif listGetAt(colShowLenList,colNo3)>(#listGetAt(colLenList,colNo3)#)</cfif> <cfif listGetAt(colNullsList,colNo3) eq "N">NOT NULL</cfif></cfloop>
)
</textarea>
</p>
<p>
Run this script on database server?
<input type="checkbox" name="isRunScript" value="1" />
</p>
<p>New MS datasource to run on<input type="text" name="dsnNew" value="#dsnNew#"><br />
(dsn user needs ownership role in order to create tables)
</p>
<p><input type="Submit" value="Next"></p>
<input type="Hidden" name="tableName" value="#tableName#">
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="colNamesList" value="#colNamesList#">
<input type="Hidden" name="colTypesNewList" value="#colTypesNewList#">
<input type="Hidden" name="colIdentityList" value="#colIdentityList#">
<input type="Hidden" name="colNullsList" value="#colNullsList#">
<input type="Hidden" name="dsnNew" value="#dsnNew#">
<input type="Hidden" name="mode" value="create">
</form>
</cfif>
<cfif mode eq "create">
<form action="#cgi.SCRIPT_NAME#?requestTimeout=#max(600,getCount.Records)#" method="post" name="form1">
<cfif val(isRunScript)>
<p>Create script has been run</p>
</cfif>
<p>Do you want to migrate #getCount.Records# records now?</p>
<p>New MS datasource to migrate to <input type="text" name="dsnNew" value="#dsnNew#"><br />
(dsn user needs ownership role in order to create tables)
</p>
<p><input type="Submit" value="Yes"></p>
<input type="Hidden" name="tableName" value="#tableName#">
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="colNamesList" value="#colNamesList#">
<input type="Hidden" name="colTypesNewList" value="#colTypesNewList#">
<input type="Hidden" name="colIdentityList" value="#colIdentityList#">
<input type="Hidden" name="colNullsList" value="#colNullsList#">
<input type="Hidden" name="mode" value="migrate">
</form>
</cfif>
<cfif mode eq "migrate">
<p>Migration complete</p>
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p><input type="Submit" value="Start Over"></p>
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="mode" value="start">
</form>
</cfif>
</cfoutput>
<!--- ALAN K HOLDEN 20120201 --->
<!--- UNIVERSALLY REQUIRED PARAMS - START --->
<!--- used for validation messages --->
<cfset pageMessage = "">
<cfparam name="mode" default="start" />
<cfparam name="dsn" default="" />
<cfparam name="tableName" default="" />
<cfparam name="colNamesList" default="" />
<cfparam name="colNullsList" default="" />
<cfparam name="colTypesList" default="" />
<cfparam name="colLenList" default="" />
<cfparam name="colTypesNewList" default="" />
<cfparam name="colIdentityList" default="" />
<cfparam name="sqlScript" default="">
<cfparam name="isRunScript" default="0">
<cfparam name="dsnNew" default="" />
<!--- UNIVERSALLY REQUIRED PARAMS - END --->
<!--- TEMPLATE (PRE-HTML)- START --->
<!---
Here, process modes are in reverse order, so if any mode fails validation
then control can revert to the earlier form mode for user correction.
Below, the actual display form modes are in the same order as seen by the user.
--->
<cfif mode eq "migrate">
<!--- this last mode does the actual data migration from old to new table --->
<cfset success = 1>
<cfif success>
<!--- get columns in this table --->
<cfquery name="getRecords" datasource="#dsn#">
SELECT #colNamesList# FROM #tableName#
</cfquery>
<cfloop query="getRecords">
<!--- determine which columns in this row to actually insert --->
<cfset insertColNamesList = colNamesList/>
<cfset insertColTypesList = colTypesNewList/>
<cfloop list="#colNamesList#" index="colName">
<cfset colNo1 = listFind(colNamesList,colName)>
<cfset colValue = getRecords[colName] />
<cfif (listGetAt(colIdentityList,colNo1))
OR (listGetAt(colNullsList,colNo1) eq 'Y' AND NOT len(colValue))
OR (isNull(colValue))
OR (listGetAt(colTypesNewList,colNo1) eq "datetime" and NOT isValid('date',colValue))
>
<cfset insertColTypesList = listDeleteAt(insertColTypesList,listFind(insertColNamesList,colName)) />
<cfset insertColNamesList = listDeleteAt(insertColNamesList,listFind(insertColNamesList,colName)) />
</cfif>
</cfloop>
<!--- issue the actual SQL command to insert the columns determined above --->
<cfquery name="putRecord" datasource="#dsnNew#">
INSERT INTO #tableName#
(#insertColNamesList#)
VALUES
(
<cfloop from="1" to="#ListLen(insertColNamesList)#" index="colNo2">
<cfset colValue = getRecords[listGetAt(insertColNamesList,colNo2)] />
<cfif colNo2 gt 1>,</cfif><cfswitch expression="#listGetAt(insertColTypesList,colNo2)#">
<!--- use type to set the correct format to insert the value (determined in 'columns' mode below) --->
<cfcase value="varchar">'#colValue#'</cfcase>
<cfcase value="datetime">#createODBCDateTime(colValue)#</cfcase>
<!--- add more cases as needed --->
<cfdefaultcase>#colValue#</cfdefaultcase>
</cfswitch>
</cfloop>
)
</cfquery>
</cfloop>
</cfif>
<cfif NOT success>
<cfset mode = "start">
</cfif>
</cfif>
<cfif mode eq "create">
<!--- this mode runs the SQL table 'CREATE' script that was composed below --->
<cfset success = 1>
<cfif findNoCase('DROP',sqlScript)>
<cfset pageMessage = "DROP is not allowed in your script." />
<cfset success = 0 />
</cfif>
<cfif findNoCase('GRANT',sqlScript)>
<cfset pageMessage = "GRANT is not allowed in your script." />
<cfset success = 0 />
</cfif>
<cfif ucase(dsnNew) eq ucase(dsn)>
<cfset pageMessage = "Cant run this on the old Oracle datasource." />
<cfset success = 0 />
</cfif>
<cfif not findNoCase('IDENTITY',sqlScript)>
<cfset colIdentityList = replace(colIdentityList,'1','0','all') />
</cfif>
<cfif success>
<cfif val(isRunScript)>
<cfquery name="runCreate" datasource="#dsnNew#">
#sqlScript#
</cfquery>
</cfif>
<cfquery name="getCount" datasource="#dsn#">
SELECT COUNT(0) as Records
FROM #tableName#
</cfquery>
</cfif>
<cfif NOT success>
<cfset mode = "columns">
</cfif>
</cfif>
<cfif mode eq "columns">
<!--- this mode parses the old tables columns to write the SQL CREATE script to make the new table --->
<cfset success = 1>
<cfif success>
<cfquery name="getColumns" datasource="#dsn#">
SELECT
column_name,
nullable,
data_type,
data_length
FROM user_tab_columns
WHERE table_name='#tableName#'
</cfquery>
<cfset colNamesList = valueList(getColumns.column_name) />
<cfset colNullsList = valueList(getColumns.nullable) />
<cfset colTypesOrigList = valueList(getColumns.data_type) />
<cfset colLenList = valueList(getColumns.data_length) />
<cfset colTypesNewList = '' />
<cfset colIdentityList = '' />
<cfset colShowLenList = '' />
<cfloop list="#colTypesOrigList#" index="origType">
<!--- look at the old Oracle type to guess/set the new MS SQL type (add more cases as needed) --->
<cfswitch expression="#ucase(left(origType,7))#">
<cfcase value="VARCHAR">
<cfset newType = "varchar" />
</cfcase>
<cfcase value="DATE">
<cfset newType = "datetime" />
</cfcase>
<cfcase value="NUMBER">
<cfset newType = "int" />
</cfcase>
<!--- add more cases as needed --->
<cfdefaultcase>
<cfset newType = "--- UNKNOWN ---" />
</cfdefaultcase>
</cfswitch>
<cfset colTypesNewList = listAppend(colTypesNewList,newType) />
<cfif newType eq "int" AND NOT listFind(colIdentityList,1)>
<cfset colIdentityList = listAppend(colIdentityList,1) />
<cfelse>
<cfset colIdentityList = listAppend(colIdentityList,0) />
</cfif>
<cfif newType eq "varchar" >
<cfset colShowLenList = listAppend(colShowLenList,1) />
<cfelse>
<cfset colShowLenList = listAppend(colShowLenList,0) />
</cfif>
</cfloop>
<cfquery name="getCount" datasource="#dsn#">
SELECT COUNT(0) as Records
FROM #tableName#
</cfquery>
</cfif>
<cfif NOT success>
<cfset mode = "tables">
</cfif>
</cfif>
<cfif mode eq "tables">
<!--- this mode fetches all the tables in the provided dsn. so user can pick one --->
<cfset success = 1>
<cfif success>
<cfquery name="getTables" datasource="#dsn#">
select table_name from all_tables
order by table_name
</cfquery>
</cfif>
<cfif NOT success>
<cfset mode = "start">
</cfif>
</cfif>
<!--- TEMPLATE (PRE-HTML) - END --->
<!--- TEMPLATE HTML- START --->
<cfoutput>
<cfif len(pageMessage)><p style="color:red;font-size:smaller;font-weight:bolder;">#pageMessage#</p></cfif>
<cfif mode eq "start">
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p>Oracle datasource / database<input type="text" name="dsn" value="#dsn#"></p>
<p><input type="Submit" value="Next"></p>
<input type="Hidden" name="mode" value="tables">
</form>
</cfif>
<cfif mode eq "tables">
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p>Select table
<select name="tableName" size="28">
<cfloop query="getTables">
<option value="#getTables.table_name#">#getTables.table_name#</option>
</cfloop>
</select>
</p>
<p><input type="Submit" value="Next"></p>
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="mode" value="columns">
</form>
</cfif>
<cfif mode eq "columns">
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p>Records: #getCount.Records#</p>
<p>
<textarea name="sqlScript" cols="60" rows="30">CREATE TABLE [dbo].[#tableName#](
<cfloop from="1" to="#listLen(colNamesList)#" index="colNo3">
<cfif colNo3 gt 1>
,</cfif>[#listGetAt(colNamesList,colNo3)#] [#listGetAt(colTypesNewList,colNo3)#] <cfif listGetAt(colIdentityList,colNo3)>IDENTITY(1,1)</cfif> <cfif listGetAt(colShowLenList,colNo3)>(#listGetAt(colLenList,colNo3)#)</cfif> <cfif listGetAt(colNullsList,colNo3) eq "N">NOT NULL</cfif></cfloop>
)
</textarea>
</p>
<p>
Run this script on database server?
<input type="checkbox" name="isRunScript" value="1" />
</p>
<p>New MS datasource to run on<input type="text" name="dsnNew" value="#dsnNew#"><br />
(dsn user needs ownership role in order to create tables)
</p>
<p><input type="Submit" value="Next"></p>
<input type="Hidden" name="tableName" value="#tableName#">
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="colNamesList" value="#colNamesList#">
<input type="Hidden" name="colTypesNewList" value="#colTypesNewList#">
<input type="Hidden" name="colIdentityList" value="#colIdentityList#">
<input type="Hidden" name="colNullsList" value="#colNullsList#">
<input type="Hidden" name="dsnNew" value="#dsnNew#">
<input type="Hidden" name="mode" value="create">
</form>
</cfif>
<cfif mode eq "create">
<form action="#cgi.SCRIPT_NAME#?requestTimeout=#max(600,getCount.Records)#" method="post" name="form1">
<cfif val(isRunScript)>
<p>Create script has been run</p>
</cfif>
<p>Do you want to migrate #getCount.Records# records now?</p>
<p>New MS datasource to migrate to <input type="text" name="dsnNew" value="#dsnNew#"><br />
(dsn user needs ownership role in order to create tables)
</p>
<p><input type="Submit" value="Yes"></p>
<input type="Hidden" name="tableName" value="#tableName#">
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="colNamesList" value="#colNamesList#">
<input type="Hidden" name="colTypesNewList" value="#colTypesNewList#">
<input type="Hidden" name="colIdentityList" value="#colIdentityList#">
<input type="Hidden" name="colNullsList" value="#colNullsList#">
<input type="Hidden" name="mode" value="migrate">
</form>
</cfif>
<cfif mode eq "migrate">
<p>Migration complete</p>
<form action="#cgi.SCRIPT_NAME#" method="post" name="form1">
<p><input type="Submit" value="Start Over"></p>
<input type="Hidden" name="dsn" value="#dsn#">
<input type="Hidden" name="mode" value="start">
</form>
</cfif>
</cfoutput>
This does not preserve original PK values, only marks the first Int as an Identity (something you can easily remove yourself in step 3). This is not a robust migration tool. If you need one, here are some links for you:
http://www.dbnetcopy.com (the source for my article image!)
http://searchstoragechannel.techtarget.com/feature/Top-five-data-migration-tools
http://www.ibm.com/developerworks/data/downloads/migration/mtk/