Tuesday, 27 October 2020
Main Menu
Web Links
Blog Links
Musical Links
Nerdery Links
Other Neato Links
Copying data from one database to another using ADO.NET...
User Rating: / 18
Written by WATYF on Monday, 07 November 2005 (25235 hits)
Category: .NET Programming

I had a little task I wanted to perform. And this was one of those times where it wasn't as easy as my little pea-brain had imagined it would be. Basically, I wanted to copy data from a table in one database into an identical table in another database. You'd think that there would be a very straightforward way to do this using the amazing new ADO.NET... and believe it or not... there is.

"So what's the problem?", you ask? Well... apparently... NO ONE ON THE ENTIRE FREAKING INTERNET HAS EVER POSTED A SAMPLE OF HOW TO DO IT!!%#!%!

*aaah*... and that's where I come in....

My first inclination of how to do this was to pull the data from the source db into a datatable. And that part is pathetically simple... but that's about where the "pathetically simple" part of this whole ordeal ended. Once you have the data in a datatable, you think to yourself, "Great! It'll only take me five minutes to figure out how to get this simple little datatable into my target database." And then you'll spend the next 3 days pounding your face against your monitor in frustration because you're so close to getting the data where you want it, but you JUST..... CAN'T..... DO IT...!!!

But since I'm such a swell guy, I'm gonna save you the pain (not to mention the mess it would make on your monitor) by telling you how.  But first... I'm gonna whine a little about how annoying it was to find this solution.

In a previous article, I complained about how people on internet techie message boards are pretty much worthless when it comes to... well... just about everything.  And this was no exception. After Googling for quite a while, I didn't come up with any suggestions on how I would do this... so I switched to Google Groups. After a number of searches, I found that a LOT of other people had the same problem as me... and the more threads I read about this problem, the more frustrating it was... because no one offered a solution. Not one time did someone say "Here's exactly how to do it.", and then proceed to post code showing you how. There was just a bunch of hem'ing and haw'ing and general idiocy.

Take this poor guy for example. He wrote a very detailed post about what he was trying to do, and the solutions he had tried so for. And, without fail, the first reply was a suggestion of how to do something entirely different than what he was asking (no surprise there, of course ). So he politely restates his question... and the next reply involves someone telling him how to do one of the solutions that he clearly stated in his initial post that he was already using. So he posts again trying to get an answer that wasn't already in his initial post, but no one ever responds.

And that's pretty much how all the other threads went. Someone would post a question, then some chuckle-head would post a not-even-close solution, or make a suggestion so vague that you'd need an "Idiot to English" translation just to figure it out.

The thing is... I had the basic structure built pretty early on. I populated a DataTable from the source db, then I created a DataAdapter to the target db, and ran the DataAdapter's Update method using the DataTable. But the data wasn't getting passed to the target database and I couldn't figure out why. I kept reading off-handed references and vague mentions of things that you needed to do, such as setting the AcceptChangesDuringFill property to False, and using an InsertCommand, but, again... no one posted a sample of what they were talking about or how to implement it.

So I went to my fall-back option of searching Experts-Exchange... a site which I don't use much any more because they charge you to search their knowledgebase... (ha! charging money for information on the net... that's crazy talk!). That's OK, though, because there's a "back-door" way to search it (hint: it starts with a G and ends with oogle). So, after a bit of searching, I found a question by a guy who was trying to do the exact same thing I wanted to do, and after reading the post I noticed an oddly familiar name at the bottom... it was MINE!?!?  Apparently, I had needed this same solution once before... but the solution I had figured out at the time (with no help from the people that posted suggestions, btw ) couldn't be used for my current situation (otherwise, that's what I would have done in the first place). BUT... one of the people who posted a suggestion in that thread had actually INCLUDED SOME CODE (*gasp*... how unorthodox), and one of his code snippets included an example of the usage of InsertCommand that I had heard about in a number of other places... so, once I worked that into my test code, voilà!! My data was copied.

So without further ado (or complaining), here's a quick, simple way to copy data from one database to another (or from a datatable to a database). In the following code, "con1" is a open ADO.NET connection to the source db, and "con2" is the connection to the target db.

        Dim da1, da2 As DataAdapter, dt As DataTable, cmd As
            'Create DataAdapter for source db
            da1 = New DataAdapter("SELECT * FROM MyTable", con1)
            'Set "AcceptChages" to False so that all rows in the
DataTable will be seen as "Added" rows
            da1.AcceptChangesDuringFill = False
            'Fill the DataTable with data from the source db
            dt = New DataTable
            If Not dt Is Nothing Then
                'Create DataAdapter for target db
                da2 = New DataAdapter("SELECT * FROM MyTable", con2)
                'Create a command builder to create the insert
                cmd = New CommandBuilder(da2)
                'Set the InsertCommand of your target DataAdapter using
the commandbuilder
                da2.InsertCommand = cmd.GetInsertCommand
                'Update the target DataAdapter using the DataTable that
you filled from the source db
            End If

As you can see, the solution itself is not difficult at all... it's just a matter of finding out how to put the pieces together... a process that can, oft times, be quite agonizing when dealing with the helpful folks on the internet.

(I say this, having learned almost everything I know about programming from intenet resources... but that's beside the point.)



(P.S. I forgot to mention... if you're using the Jet engine with ADO.NET (or ADO or ODBC or whatever) you can transfer data between databases using a single SQL statement:

INSERT INTO MyOtherTable IN 'C:\My Files\MyOtherDB.vdb' SELECT * FROM MyTable
(appends to existing table in another db)


SELECT * INTO MyOtherTable IN 'C:\My Files\MyOtherDB.vdb' FROM MyTable
(creates new table in another db)

...but I'm not using Jet in this instance, otherwise I would have used those statements in the first place.)

< Prev   Next >


You must javascript enabled to use this form

I have tried your sollution but in the end the result is that nothig happens....no data is inserted in the new table from the new database...
The only difference is that I used a DataSet instead of a DataTable...shouldn't it be the same thing?

Posted by Alex, on 03/27/2007 at 03:58

No... a DataSet and DataTable are not the same thing and will not function similarly. If you want to use a DataSet, you'll need to add a DataTable to it and fill the DataTable.... but in all honesty, if all you're trying to do is copy one table to another db, then you don't need a DataSet. WATYF

Posted by WATYF, on 03/27/2007 at 08:14

SELECT * INTO MyOtherTable IN 'C:My FilesMyOtherDB.vdb' FROM MyTable
(creates new table in another db)

I couldn't for the life of me remember the syntax for the above so gave up and decided to try copying data using ADO.net so googled your article and found what I had wanted in the first place.

All useful though!

Posted by Dan, whose homepage is here on 09/06/2007 at 05:40

That was MUY helpful code you posted, and I REALLY enjoyed - and identified with - your 'endless search for the solution' frustrations.

It seems like many people today are more concerned with making themselves sound like eggheads rather than actually writing some useful examples (kinda like the textbooks...). I've run into that, too!

Thank you again,

Posted by Myles, on 11/02/2007 at 10:34

thanks alot

Posted by imalik, on 07/07/2009 at 02:02

Excellent - I needed to do a quick transfer between two databases in an unfamiliar DBMS and this sample was precisely what I needed. You've just made me feel very lazy. Thanks!

Posted by Gavin, on 07/23/2009 at 03:20

Excuse my English, I Speak Spanish.
Thank you very much, too much time I've spent looking for something similar to this example you have post.

I have to do something similar to this, from Access DB as Source to MySQL as target, but I have a question.

If I wanna Import not all Rows but Rows no present in MYSQL table that are in Access Table I hope you understand my question

Thanks in Advance

Posted by yovanys, on 10/15/2009 at 10:12

I don't know exactly how you would insert only the records that don't already exist in the target db. My first thought would be to pull the table from the target db and load it into a datatable, and then use the contents of that datatable to delete any matching records in the first datatable before performing the insert: da2.Update(dt). But that's just the first thing to pop in my head. There's probably a better way to go about it.

Posted by WATYF, on 10/16/2009 at 07:28

The code was excellent and upto the mark. I had run it and it just copied data from an ACCESS 2007 database to MS SQL Server express database within few seconds.

Appreciated a lot, Really great help !!!

Thanks and Cheers'

Posted by yd, on 05/22/2010 at 07:46

Thanks for the code, Heres my mod for SQL server to Access:

Dim con1 As SqlConnection
con1 = New SqlConnection

Dim con2 As OleDbConnection
con2 = New OleDbConnection

con1.ConnectionString = strConnectionStringWeb
con2.ConnectionString = strConnectionStringSource

Dim da1 As SqlDataAdapter, dt As DataTable, cmd As SqlCommandBuilder
Dim cmd2 As OleDbCommandBuilder
Dim da2 As OleDbDataAdapter
'Create DataAdapter for source db
da1 = New SqlDataAdapter('SELECT * FROM web_product_forum', con1)
'Set 'AcceptChages' to False so that all rows in the DataTable will be seen as 'Added' rows
da1.AcceptChangesDuringFill = False
'Fill the DataTable with data from the source db
dt = New DataTable
If Not dt Is Nothing Then
'Create DataAdapter for target db
da2 = New OleDbDataAdapter('SELECT * FROM web_product_forum', con2)
'Create a command builder to create the insert statements()
cmd2 = New OleDbCommandBuilder(da2)
'Set the InsertCommand of your target DataAdapter using the(commandbuilder)
da2.InsertCommand = cmd2.GetInsertCommand
'Update the target DataAdapter using the DataTable that you filled from the source db
End If

Posted by MarkW, on 01/31/2011 at 05:31

Page 1 of 1 ( 10 Comments )
©2007 MosCom


© 2020 Musical Nerdery
Joomla! is Free Software released under the GNU/GPL License.