Hi
I have a DataTable with thousands of rows. I want a quick way to insert the
rows into a table in an Access database with the same structure. I have been
using an update command but found it to be very slow i.e. there are about
100,000 rows of data.
Can anybody suggest a quick way to do it?
Geoff 11 1771
"Geoff" <no********@email.com> wrote in message
news:43***********************@news.dial.pipex.com ... I have a DataTable with thousands of rows. I want a quick way to insert the rows into a table in an Access database with the same structure.
.. . . there are about 100,000 rows of data
Personally, I'd dump it out into a suitable, flat file and use a
"bulk load" utility rather than trying to write it myself.
IIRC, Access uses something like "get external data" ...
HTH,
Phill W.
Geoff,
If you want to do it using a program because it is not a one time approach
(otherwise the way Phil suggest is for me as well the one to go), than I
would not go for the dataadapter however for a datareader and Inserts.
(If the access table is off line than can the xml dataset and your approach
of course be your friend).
I hope this helps,
Cor
"Geoff" <no********@email.com> schreef in bericht
news:43***********************@news.dial.pipex.com ... Hi
I have a DataTable with thousands of rows. I want a quick way to insert the rows into a table in an Access database with the same structure. I have been using an update command but found it to be very slow i.e. there are about 100,000 rows of data.
Can anybody suggest a quick way to do it?
Geoff
Hi Cor
I'm afraid I don't follow. What do you mean by "not go for the dataadapter
however for a datareader and Inserts"?
The access table is off-line i.e. on the computer that the application is
running. I'm intrigued by the xml dataset you mention. Could you give a code
example?
Thanks in advance
Geoff
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Geoff,
If you want to do it using a program because it is not a one time approach (otherwise the way Phil suggest is for me as well the one to go), than I would not go for the dataadapter however for a datareader and Inserts.
(If the access table is off line than can the xml dataset and your approach of course be your friend).
I hope this helps,
Cor
"Geoff" <no********@email.com> schreef in bericht news:43***********************@news.dial.pipex.com ... Hi
I have a DataTable with thousands of rows. I want a quick way to insert the rows into a table in an Access database with the same structure. I have been using an update command but found it to be very slow i.e. there are about 100,000 rows of data.
Can anybody suggest a quick way to do it?
Geoff
"Geoff" <no********@email.com> schreef in bericht
news:43**********************@news.dial.pipex.com. .. Hi Cor
I'm afraid I don't follow. What do you mean by "not go for the dataadapter however for a datareader and Inserts"?
The access table is off-line i.e. on the computer that the application is running. I'm intrigued by the xml dataset you mention. Could you give a code example?
Not to day anymore, however basicly the key points are.
You need while creating the dataset to set in the reading dataadapter the
property
acceptchangesduringfill = false
A second point you have to know if there are alreayde rows with the same key
in the other dataset.
Very very roughly typed here it is.
Dim conn as new olledbconnection(connections string)
dim da as new oledbdataadapter("Select * from table",conn)
da.acceptchangesduringfill = false
dim ds as new dataset
da.fill(mytable)
ds.writexml(path, schema.....) look for the intelisence for that schema
in the writing program-----------------------
Dim conn as new olledbconnection(connections string)
dim da as new oledbdataadapter("Select * from table",conn)
dim ds as new dataset
ds.readxml(path)
dim cmb as new commandbuilder(da)
da.update(mytable)
You can give it a try, however friday eve is not the best time to take
samples from me.
:-)
Cor
Geoff - I wrote an article about this a while ago http://www.knowdotnet.com/articles/datasetmerge.html and it's in VB.NET.
Essentially what you do is configure two data adapters, one for the source,
one for the destination table. Then, set the AcceptChangesDuringFill
property of th efirst one to false. This will fill the datatable and set
the rowstate of each row to Added. Then use another dataadpter to point to
the destination table and call it's Update method, passing in the exact same
datatable you just filled. This will cause the adapter to fire the Insert
command for each row, thereby adding the rows to the destination table.
However, this is terribly inefficient as far as data transfer technologies
go. Depending on what tools you have available though - it may be a decent
choice. If you're dealing with large amounts of data though - I'd
definitely opt for another approach - ideally DTS.
In the 2.0 Framework, tehre's a SqlBulkCopy object that does this amazingly
quickly - http://msmvps.com/williamryan/archiv...7/10/9890.aspx
however it's only for Sql Server so probably won't serve your needs in this
case, I mention it only as a point of reference.
HTH,
Bill
"Geoff" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. .. Hi Cor
I'm afraid I don't follow. What do you mean by "not go for the dataadapter however for a datareader and Inserts"?
The access table is off-line i.e. on the computer that the application is running. I'm intrigued by the xml dataset you mention. Could you give a code example?
Thanks in advance
Geoff
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl... Geoff,
If you want to do it using a program because it is not a one time approach (otherwise the way Phil suggest is for me as well the one to go), than I would not go for the dataadapter however for a datareader and Inserts.
(If the access table is off line than can the xml dataset and your approach of course be your friend).
I hope this helps,
Cor
"Geoff" <no********@email.com> schreef in bericht news:43***********************@news.dial.pipex.com ... Hi
I have a DataTable with thousands of rows. I want a quick way to insert the rows into a table in an Access database with the same structure. I have been using an update command but found it to be very slow i.e. there are about 100,000 rows of data.
Can anybody suggest a quick way to do it?
Geoff
Hi Cor
I don't blame you - I have a bottle of wine to open as well! :)
I'll try and understand the code you've sent.
Geoff
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:eb*************@TK2MSFTNGP10.phx.gbl... "Geoff" <no********@email.com> schreef in bericht news:43**********************@news.dial.pipex.com. .. Hi Cor
I'm afraid I don't follow. What do you mean by "not go for the dataadapter however for a datareader and Inserts"?
The access table is off-line i.e. on the computer that the application is running. I'm intrigued by the xml dataset you mention. Could you give a code example? Not to day anymore, however basicly the key points are.
You need while creating the dataset to set in the reading dataadapter the property
acceptchangesduringfill = false
A second point you have to know if there are alreayde rows with the same key in the other dataset.
Very very roughly typed here it is.
Dim conn as new olledbconnection(connections string) dim da as new oledbdataadapter("Select * from table",conn) da.acceptchangesduringfill = false dim ds as new dataset da.fill(mytable) ds.writexml(path, schema.....) look for the intelisence for that schema
in the writing program----------------------- Dim conn as new olledbconnection(connections string) dim da as new oledbdataadapter("Select * from table",conn) dim ds as new dataset ds.readxml(path) dim cmb as new commandbuilder(da) da.update(mytable)
You can give it a try, however friday eve is not the best time to take samples from me.
:-)
Cor
Cor - I'm not arguing with you - but why write the data to XML when you
already have it right there? Won't this just add file IO overhead?
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:eb*************@TK2MSFTNGP10.phx.gbl... "Geoff" <no********@email.com> schreef in bericht news:43**********************@news.dial.pipex.com. .. Hi Cor
I'm afraid I don't follow. What do you mean by "not go for the dataadapter however for a datareader and Inserts"?
The access table is off-line i.e. on the computer that the application is running. I'm intrigued by the xml dataset you mention. Could you give a code example? Not to day anymore, however basicly the key points are.
You need while creating the dataset to set in the reading dataadapter the property
acceptchangesduringfill = false
A second point you have to know if there are alreayde rows with the same key in the other dataset.
Very very roughly typed here it is.
Dim conn as new olledbconnection(connections string) dim da as new oledbdataadapter("Select * from table",conn) da.acceptchangesduringfill = false dim ds as new dataset da.fill(mytable) ds.writexml(path, schema.....) look for the intelisence for that schema
in the writing program----------------------- Dim conn as new olledbconnection(connections string) dim da as new oledbdataadapter("Select * from table",conn) dim ds as new dataset ds.readxml(path) dim cmb as new commandbuilder(da) da.update(mytable)
You can give it a try, however friday eve is not the best time to take samples from me.
:-)
Cor
Bill, Cor - I'm not arguing with you - but why write the data to XML when you already have it right there? Won't this just add file IO overhead?
I don't blaim you however obvious did you not read the complete thread.
:-)
I understood from Geoff after telling some alternatives that it is to
transport to an offline computer and that it is not a one time operatinon.
:-)
Cor
Hi Ryan
Thank you so much for you insightful comments.
As I have indicated in previous posts, I am surprised that the Update
command is so "slow". I accept that when I say "slow" maybe others would say
it isn't but I will give the following example which may indicate the
reasons why I think I'm doing something wrong:
As I mentioned before, I have a large file which I'm attempting to transfer
to a table in an Access database. This data originally exists as a cvs file
and I've written a routine to load the data into a dataset, manipulate it,
and then transfer it to a table in the database using the Update command
i.e.
MyDataAdaptor.Update(MyDataSet)
This works BUT it takes simply ages; we're talking hours here to transfer
around 100,000 rows of data.
However, if use an inbuilt Access tool like TransferText it takes minutes!!!
Can you see my confusion? As far as I can see, the same job is being done
but the difference is dramatic!!!
Either there is something very obvious I'm missing or the Update is very
inefficient (but I don't believe this - I'm more inclined to think that I'm
doing something wrong).
Any comments would be gratefully received.
Geoff
"W.G. Ryan - MVP" <Wi*********@nospam.gmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Geoff - I wrote an article about this a while ago http://www.knowdotnet.com/articles/datasetmerge.html and it's in VB.NET. Essentially what you do is configure two data adapters, one for the source, one for the destination table. Then, set the AcceptChangesDuringFill property of th efirst one to false. This will fill the datatable and set the rowstate of each row to Added. Then use another dataadpter to point to the destination table and call it's Update method, passing in the exact same datatable you just filled. This will cause the adapter to fire the Insert command for each row, thereby adding the rows to the destination table.
However, this is terribly inefficient as far as data transfer technologies go. Depending on what tools you have available though - it may be a decent choice. If you're dealing with large amounts of data though - I'd definitely opt for another approach - ideally DTS.
In the 2.0 Framework, tehre's a SqlBulkCopy object that does this amazingly quickly - http://msmvps.com/williamryan/archiv...7/10/9890.aspx however it's only for Sql Server so probably won't serve your needs in this case, I mention it only as a point of reference.
HTH,
Bill "Geoff" <no********@email.com> wrote in message news:43**********************@news.dial.pipex.com. .. Hi Cor
I'm afraid I don't follow. What do you mean by "not go for the dataadapter however for a datareader and Inserts"?
The access table is off-line i.e. on the computer that the application is running. I'm intrigued by the xml dataset you mention. Could you give a code example?
Thanks in advance
Geoff
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl... Geoff,
If you want to do it using a program because it is not a one time approach (otherwise the way Phil suggest is for me as well the one to go), than I would not go for the dataadapter however for a datareader and Inserts.
(If the access table is off line than can the xml dataset and your approach of course be your friend).
I hope this helps,
Cor
"Geoff" <no********@email.com> schreef in bericht news:43***********************@news.dial.pipex.com ... Hi
I have a DataTable with thousands of rows. I want a quick way to insert the rows into a table in an Access database with the same structure. I have been using an update command but found it to be very slow i.e. there are about 100,000 rows of data.
Can anybody suggest a quick way to do it?
Geoff
Sorry Cor, I've misunderstood what you meant by "off line". What I meant was
that the database is not connected via an ethernet or interenet link; rather
it is simply on the computer that the computer has the application is
running.
However, the comments about XML were most useful - and have helped me with
another unrelated problem so not all is lost.
Geoff
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e7***************@tk2msftngp13.phx.gbl... Bill,
Cor - I'm not arguing with you - but why write the data to XML when you already have it right there? Won't this just add file IO overhead?
I don't blaim you however obvious did you not read the complete thread.
:-)
I understood from Geoff after telling some alternatives that it is to transport to an offline computer and that it is not a one time operatinon.
:-)
Cor
Bill,
I would be curious if the Update would still be as slow with the new Batch
Update abilities of .NET 2.0. http://msdn2.microsoft.com/en-us/lib...US,VS.80).aspx
I would expect it to be slower then SqlBulkCopy but faster then .NET 1.1
updates.
Of course, based on the above link, batch updates do not appear to be
supported with Access...
--
Hope this helps
Jay
T.S. Bradley - http://www.tsbradley.net
"W.G. Ryan - MVP" <Wi*********@nospam.gmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
| Geoff - I wrote an article about this a while ago
| http://www.knowdotnet.com/articles/datasetmerge.html and it's in VB.NET.
| Essentially what you do is configure two data adapters, one for the
source,
| one for the destination table. Then, set the AcceptChangesDuringFill
| property of th efirst one to false. This will fill the datatable and set
| the rowstate of each row to Added. Then use another dataadpter to point
to
| the destination table and call it's Update method, passing in the exact
same
| datatable you just filled. This will cause the adapter to fire the Insert
| command for each row, thereby adding the rows to the destination table.
|
| However, this is terribly inefficient as far as data transfer technologies
| go. Depending on what tools you have available though - it may be a
decent
| choice. If you're dealing with large amounts of data though - I'd
| definitely opt for another approach - ideally DTS.
|
| In the 2.0 Framework, tehre's a SqlBulkCopy object that does this
amazingly
| quickly - http://msmvps.com/williamryan/archiv...7/10/9890.aspx
| however it's only for Sql Server so probably won't serve your needs in
this
| case, I mention it only as a point of reference.
|
| HTH,
|
| Bill This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: VM |
last post by:
How can I copy the contents of one datatable to another datatable? I've
already the source table so I only need to copy its rows.
Thanks.
|
by: VMI |
last post by:
I have a filled datatable and I need to copy this data into an Access table
with exactly the same table structure. Can I do this without having to run
an Insert query for every line of the...
|
by: Nedu N |
last post by:
Hi Techies,
I am facing problem in copying content of table from a untyped dataset into
to a table inside the typed dataset. I wanted to copy the data into typed
dataset in order to ease the...
|
by: Nathan Sokalski |
last post by:
I am writing an ASP.NET application in which I need to copy DataRows from
one DataTable to another. When I use code such as the following:
temprows = nodes.Select("state='PA'")...
|
by: JPO |
last post by:
Hi there,
I'm trying to use MSAccess as a "container" to move data around from
one MS-SQL server DB to another. This is basically already a design
decision that has been made for a lot of...
| |
by: byrd48 |
last post by:
Hi,
am attempting to create a DataTable by copying a table from a data
set, then filter the rows as follows:
DataTable dt = DataSet1.Tables.Copy;
DataRow dr = dt.Select("myexpression");
...
|
by: Wayne Gibson |
last post by:
Hi All,
Was wondering if anybody could help. I'm currently trying to copy a record
from datatable to another. I have two problems:
1) Transferring the record from one datatable to another.
2)...
|
by: MathewJose |
last post by:
Hi,
I have a DatagridView in windows form.It has got a column which has some names that are populated from master table in database.Now i need to copy a set of data against these names.
I...
|
by: tshad |
last post by:
Running on VS.net 2005,
I am trying to copy rows from my datatable to another datatable in the same
dataset.
The schema would be identical.
I need to make the table name "forms" as I am...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |