Plse find enclosed a script to write records from SQL Server to Oracle:
import pyodbc import datetime conn = pyodbc.connect('DSN=SQLServer;UID=sa;PWD=**') conntarget = pyodbc.connect('DSN=Rekendoos;UID=hr;PWD=**') table_name = 'MIGRATED_DIVORCE_SETTLEMENT__C' cursor = conn.cursor() cursortarget = conntarget.cursor() cursor.execute('select * from Rapportage.' + tabel) columns = [column[0] for column in cursor.description] kolommen = '' for x in range(len(columns) - 1): kolommen = kolommen + columns[x] + ', ' kolommen = kolommen + columns[len(columns) - 1] results = [columns] + [row for row in cursor.fetchall()] zoek_query = "select count(table_name) from user_tables where lower(table_name)=lower('" + table_name + "')" cursortarget.execute(zoek_query) if cursortarget.fetchone()[0]== 1: cursortarget.execute("drop table " + table_name ) stmt = 'CREATE TABLE "HR"."MIGRATED_DIVORCE_SETTLEMENT__C" ' \ + '( "ID" CLOB, ' \ + ' "BPP65_AMOUNT__C" NUMBER,' \ + ' "BPP67_AMOUNT__C" NUMBER, ' \ + ' "BPP67_VPL_AMOUNT__C" NUMBER, ' \ + ' "END_DATE_SETTLEMENT_PERIOD__C" DATE, ' \ + ' "PENSION_SCHEME_ID__C" CLOB, ' \ + ' "PERSON_ID_1__C" CLOB, ' \ + ' "PERSON_ID_2__C" CLOB, ' \ + ' "PROVIDER_ID__C" CLOB, ' \ + ' "REGISTRATION_DATE__C" DATE, ' \ + ' "SEND_DATE_TO_CORE__C" DATE, ' \ + ' "SETTLEMENT_DATE__C" DATE, ' \ + ' "START_DATE_RELATIONSHIP__C" DATE,' \ + ' "START_DATE_SETTLEMENT__C" CLOB,' \ + ' "VERSION_NUMBER_SETTLEMENT__C" CLOB, ' \ + ' "VOP65_AMOUNT__C" NUMBER, ' \ + ' "VOP67_AMOUNT__C" NUMBER, ' \ + ' "VOP67_VPL_AMOUNT__C" NUMBER,' \ + ' "VPREP__C" NUMBER, ' \ + ' "VOPHOOG__C" NUMBER, ' \ + ' "VTOP_TOT_AOW__C" NUMBER, ' \ + ' "CREATEDBYID" CLOB, ' \ + ' "CREATEDDATE" CLOB, ' \ + ' "LASTMODIFIEDBYID" CLOB, ' \ + ' "LASTMODIFIEDDATE" CLOB' \ + ' ) ' cursortarget.execute(stmt) for i in range(1,len(results)): Sql_insert_query1 = "INSERT INTO " + table_name + " (" + kolommen + ") VALUES (" Sql_insert_query2 = '' for j in range(len(columns)): uitkomst = results[i][j] if isinstance(uitkomst, str): uitkomst = uitkomst.replace("'"," ") uitkomst = "\'" + uitkomst + "\'" if uitkomst is None: uitkomst = '' uitkomst = "\'" + uitkomst + "\'" if isinstance(uitkomst, (int, float)): uitkomst = str(uitkomst) if isinstance(uitkomst, (datetime.date, datetime.datetime) ): # print(str(j) + 'date') uitkomst = str(uitkomst) uitkomst = uitkomst[0:10] uitkomst = "to_date('" + uitkomst + "','yyyy-mm-dd')" Sql_insert_query2 = Sql_insert_query2 + uitkomst if j < int(len(columns)) - 1: Sql_insert_query2 = Sql_insert_query2 + ", " Sql_insert_queryn = ")" Sql_insert_query = Sql_insert_query1+Sql_insert_query2+Sql_insert_queryn if i % 1000 == 0: print(str(i) + ' Rijen gelezen') conntarget.commit() # print(Sql_insert_query) cursortarget.execute(Sql_insert_query) conntarget.commit() print('Lezen gereed') cursor.close() conntarget.close()
Here, the results are written to a list.