Best way to map corresponding columns of two identical tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I am working on migrating data from one table to another identical table. Though the field names are same in both the tables, their order in both the tables need not be the same
Could anyone suggest a good way to identify the corresponding fields of the tables
The user may go to the database and can again alter the order of field names.The application still has to recognise the corresponding fields

Any input will be greatly appreciated

Thank Yo
Julian
 
Hi Julian,

Are you moving records across database tables or only in memory?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Julian said:
Hi,
I am working on migrating data from one table to another identical table.
Click to expand...
Though the field names are same in both the tables, their order in both the
tables need not be the same.
Could anyone suggest a good way to identify the corresponding fields of the tables ?
The user may go to the database and can again alter the order of field
Click to expand...
names.The application still has to recognise the corresponding fields.
 
Hi Julian,

Then you should execute a command like:
INSERT INTO Destination(col1, col2, ...) SELECT col1, col2... FROM source
It is certainly the fastest way.

If you don't know the column names, you should use GetOleDbScema method
perhaps.
What database server are you talking about?
 
Hi Miha,
I am talking about Access and Oracle databases.
INSERT INTO Destination(col1, col2, ...) SELECT col1, col2... FROM source, how can I ensure that the destination col1,col2.... correspond to source col1,col2... etc.

Are you suggesting using the GetOleDbScema method to get the column names of source and then do a string comparison to see whether the column names match with destination ?

Thank You
Julian
 
Hi,

A, you want transfer data between different databases then.
If the both tables have identical columns and are not very large, you might
do the following (assuming Access is the source).
Create a source OleDbDataAdapter (only SelectCommand necessary) and a
destination OracleDataAdapter (if you are only inserting data then only
InsertCommand is necessary).
pseudo code:
DataTable dt = new DataTable();
sourceAdapter.AcceptChangesDuringFill = false;
sourceAdapter.Fill(dt);
destAdapter.Update(dt);

This will load data into memory and insert all rows into oracle database.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Julian said:
Hi Miha,
I am talking about Access and Oracle databases.
INSERT INTO Destination(col1, col2, ...) SELECT col1, col2... FROM source,
Click to expand...
how can I ensure that the destination col1,col2.... correspond to source
col1,col2... etc.
Are you suggesting using the GetOleDbScema method to get the column names
Click to expand...
of source and then do a string comparison to see whether the column names
match with destination ?
 
Hi Miha,
Thank You for the reply.
The method you suggested will work if the column order of both the tables are same. What if the corresponding columns of the two tables are not in the same order in the two databases ?
For eg. if col1 of access table is same as col3 of oracle etc.

Thank You
Julian
 
Hi Julian,

The column order doesn't matter at all.
Parameters are mapped to columns normally.
See IDataParameter.SourceColumn property (parameters implement
IDataParameter interface).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Julian said:
Hi Miha,
Thank You for the reply.
The method you suggested will work if the column order of both the tables
Click to expand...
are same. What if the corresponding columns of the two tables are not in the
same order in the two databases ?
 
Hi Miha,
One last question. You said about
-source OleDbDataAdapter (only SelectCommand necessary) and a
- destination OracleDataAdapter (if you are only inserting data then only InsertCommand is necessary).

I have two questions -

1) I have about 97 fields in both tables. So while giving the InsertCommand, is there an easy way to generate sql ?

The SelectCommand is going to be (select * from table)

2) I created two datatables with one of the table(dtableAccess), in the fillmethod of the source OleDbDataAdapter.

sourceOleDbAdapter.Fill(dtableAccess)

The other table i made as

dtableOracle=dtableAccess.copy();

Thus I have all the values in the second table. Now my doubt is how to write this table to the oracle database .


Thank You
Julian
 
Hi Julian,

Julian said:
Hi Miha,
One last question. You said about
-source OleDbDataAdapter (only SelectCommand necessary) and a
- destination OracleDataAdapter (if you are only inserting data then only InsertCommand is necessary).

I have two questions -

1) I have about 97 fields in both tables. So while giving the
Click to expand...
InsertCommand, is there an easy way to generate sql ?
The SelectCommand is going to be (select * from table)
Click to expand...

You might drag&drop table from server explore onto a form - the wizard will
create an insert (and other commands) for you.
If you don't need all of that - just copy&paste the code generated by wizard
(see Windows Form Designer generated code).
2) I created two datatables with one of the table(dtableAccess), in the
Click to expand...
fillmethod of the source OleDbDataAdapter.
sourceOleDbAdapter.Fill(dtableAccess)

The other table i made as

dtableOracle=dtableAccess.copy();

Thus I have all the values in the second table. Now my doubt is how to
Click to expand...
write this table to the oracle database .

In the first place, you don't need to create a copy table.
Just call oracleAdapter.Update(dtableAccess);
Make sure that you've set sourceAdapter.AcceptChangesDuringFill = false
before Fill method.
 
Hi Miha
When I call oracleAdapter.Update(dtableAccess),I get the following error

Unspecified error Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported.

Have you come across such an error ? Where should I start looking

Thank Yo
Julia
 
Hi,

Show me adapter definition...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Julian said:
Hi Miha,
When I call oracleAdapter.Update(dtableAccess),I get the following error.

Unspecified error Oracle error occurred, but error message could not be
Click to expand...
retrieved from Oracle. Data type is not supported.
 
Hi Miha,
This is the code snippet.Access table is the source and Oracle table is the destination.Both have got identical tables.(fields,field types etc).
One of the access field is an autonumber which is the primary key of the table.


OleDbDataAdapter accDa = new OleDbDataAdapter("select * from accessTable",accConn);
OleDbDataAdapter oraDa = new OleDbDataAdapter("select * from oracleTable",oraConn);
accDa.AcceptChangesDuringFill = false;
DataTable accTbl = new DataTable();
accDa.Fill(accTbl);
oraDa.Fill(accTbl);

Error is
Exception Details: System.Data.OleDb.OleDbException: Unspecified error Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported.

Source Error:
Line 38: oraDa.Fill(accTbl); ------- This is given in red


Stack Trace:


[OleDbException (0x80004005): Unspecified error
Oracle error occurred, but error message could not be retrieved from Oracle.
Data type is not supported.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +68
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
SecPresentation.migrate2.Page_Load(Object sender, EventArgs e) in \\iis540\Julian\migrate.aspx.cs:38
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731







Thank You
Julian
 
Hi Julian,

Oracle adapter should be configured for inserting, not for selecting, in
your case.
You might want to create a connection to oracle database in server explorer
and drag&drop the table (which is in oracle's database) from server explorer
onto the form.
The wizard will generate the code for you - see Windows Froms Designer
generated code region.
Then you should use oraDa.Update(accTbl) to insert rows into oracle (don't
forget to open the oraConn before.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


Julian said:
Hi Miha,
This is the code snippet.Access table is the source and Oracle table is
Click to expand...
the destination.Both have got identical tables.(fields,field types etc).
One of the access field is an autonumber which is the primary key of the table.


OleDbDataAdapter accDa = new OleDbDataAdapter("select * from accessTable",accConn);
OleDbDataAdapter oraDa = new OleDbDataAdapter("select * from oracleTable",oraConn);
accDa.AcceptChangesDuringFill = false;
DataTable accTbl = new DataTable();
accDa.Fill(accTbl);
oraDa.Fill(accTbl);

Error is
Exception Details: System.Data.OleDb.OleDbException: Unspecified error
Click to expand...
Oracle error occurred, but error message could not be retrieved from Oracle.
Data type is not supported.
Source Error:
Line 38: oraDa.Fill(accTbl); ------- This is given in red


Stack Trace:


[OleDbException (0x80004005): Unspecified error
Oracle error occurred, but error message could not be retrieved from Oracle.
Data type is not supported.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
Click to expand...
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +68
Click to expand...
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
Click to expand...
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
Click to expand...
command, CommandBehavior behavior)
 
Back
Top