How to write into one table while reading from another?

[Leonid Raiz]

Group,

I am relatively new to ADO.NET and am trying to figure out how to do the
following. I established OleDbConnection and now need to read all records
from one of the tables. For every record in first table I need to insert
records into other table. A simpleminded approach would be
....
readCmd.CommandText = "SELECT * FROM ReadTable";
writeCmd.CommandText = "INSERT INTO WriteTable WHERE col1 = ? AND col2 = ?";
OleDbDataReader reader = read.ExecuteReader();
while( reader.Read() )
{
....// prepare record data for WriteTable
writeCmd.ExecuteNonQuery();
}

Unfortunately such code raises exceptions when wrteCmd.ExecuteNonQuery() is
invoked because the reader is still open. How to I code this type of
operation correctly? Or is there a different strategy to achieve what I want
in a relatively efficient way? I would rather avoid the performance overhead
of bringing in entire ReadTable into memory at once.

Thanks for your help,
- LR

 

[Sahil Malik]

Leonid,

I think you are using the same connection object for both reading and
writing.

Welcome to MARS - Multiple Active Resultsets - which will allow you to
browse two open resultsets on the same connection as long as the underlying
database supports it - this will work in ADO.NET 2.0 and Sql Server 2005 for
instance.

But since you are maybe not using ADO.NET 2.0 and Sql 2k5, you would
probably have to resort to having two open connections at the same time
(specify a brand new connection object with the same connection string for
the OleDbCommand) - or fill in a datatable.

Yup these are two less than ideal choices - which is why we have a version
2.0

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[David Browne]

Group,

I am relatively new to ADO.NET and am trying to figure out how to do the
following. I established OleDbConnection and now need to read all records
from one of the tables. For every record in first table I need to insert
records into other table. A simpleminded approach would be
...
readCmd.CommandText = "SELECT * FROM ReadTable";
writeCmd.CommandText = "INSERT INTO WriteTable WHERE col1 = ? AND col2 =
?";
OleDbDataReader reader = read.ExecuteReader();
while( reader.Read() )
{
....// prepare record data for WriteTable
writeCmd.ExecuteNonQuery();
}

Unfortunately such code raises exceptions when wrteCmd.ExecuteNonQuery()
is invoked because the reader is still open. How to I code this type of
operation correctly? Or is there a different strategy to achieve what I
want in a relatively efficient way? I would rather avoid the performance
overhead of bringing in entire ReadTable into memory at once.

The correct way to do this is with a single query

insert into WriteTable(A,B,C)
select expressionA,expressionB,expressionC
from ReadTable

If you can't do this, then you need to load ReadTable into a DataSet.

David

 

[Leonid Raiz]

Thanks for your help. I indeed tried to use a single connection (to Access).
Since ADO.NET 2.0 is not an option I will try opening two connections.

 

[Leonid Raiz]

thanks, I'll give it a try

The correct way to do this is with a single query

insert into WriteTable(A,B,C)
select expressionA,expressionB,expressionC
from ReadTable

If you can't do this, then you need to load ReadTable into a DataSet.

David

 

[Paul Clement]

¤ Group,
¤
¤ I am relatively new to ADO.NET and am trying to figure out how to do the
¤ following. I established OleDbConnection and now need to read all records
¤ from one of the tables. For every record in first table I need to insert
¤ records into other table. A simpleminded approach would be
¤ ...
¤ readCmd.CommandText = "SELECT * FROM ReadTable";
¤ writeCmd.CommandText = "INSERT INTO WriteTable WHERE col1 = ? AND col2 = ?";
¤ OleDbDataReader reader = read.ExecuteReader();
¤ while( reader.Read() )
¤ {
¤ ....// prepare record data for WriteTable
¤ writeCmd.ExecuteNonQuery();
¤ }
¤
¤ Unfortunately such code raises exceptions when wrteCmd.ExecuteNonQuery() is
¤ invoked because the reader is still open. How to I code this type of
¤ operation correctly? Or is there a different strategy to achieve what I want
¤ in a relatively efficient way? I would rather avoid the performance overhead
¤ of bringing in entire ReadTable into memory at once.

If you're simply appending data from table into another table you should be able to use a single
INSERT INTO...SELECT statement.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[W.G. Ryan eMVP]

Leonid- if you must do it client side - you may want to use a DataAdapter -
set the AcceptChangesDuringFill property to false on the adapter that you
fill the datatable with. This will ensure that hte Rowstate of each row is
Inserted. Then, just use another adapter - mapping only the fields you want
(if the destination table has fewer fields than the select statement) and
call Update on the second adapter - passing in the datatable to the second
adapter.

however if you are simply moving data on the back end- I concur with the
others - Server side processing is probably much better suited for this type
of task.