Copying data from one database to another using ADO.NET...

  • Thread starter Thread starter WATYF1
  • Start date Start date
W

WATYF1

I honestly didn't think this would be such an impossible task, but it
has proven to be so. All I am trying to do is copy data from a table in
one database to an indentical table in another database. I want to do
this in order to backup my main database periodically. So, the main db
has data in it, and the bak database has empty tables (which are the
same tables/schema as the main db).

At this point, using information that I have painfully deciphered from
the vague and not-so-helpful posts I've run across so far, I was able
to hack together the following code. Basically, my approach is... fill
a datatable from one database, and update that datatable into the other
database. Unfortunately, it doesn't work... I keep getting an error.
But first, here's the code:

Dim tbls() As String = New String() {"MyTable1", "MyTable2",
"MyTable3", "MyTable4"}
For i As Integer = 0 To UBound(tbls)
'Fill datatable from main db
da = New DataAdapter("SELECT * FROM " & tbls(i), con)
da.AcceptChangesDuringFill = False
dt = New DataTable
da.Fill(dt)
da.Dispose()
If Not dt Is Nothing Then
'Create da to bak db and update it using the dt from
main db
da = New DataAdapter("SELECT * FROM " & tbls(i), bak)
da.Update(dt)
da.Dispose()
dt.Dispose()
End If
Next

The error occurs on the "da.Update(dt) line... and here's the error:

"Update requires a valid InsertCommand when passed DataRow collection
with new rows."


I've seen numerous *mentions* of using an InsertCommand to perform
operations such as this, but it seems that no one thought to actually
post any samples of HOW to do it, so I'm at a loss as to how to get
around this error.

What's troubling is, I've seen a number of posts that ask this (or a
similar) question, but I have yet to see one comprehensive solution
offered. I guess I'm just hoping to be the exception. :op


WATYF
 
You know... I figured out the solution to the problem while I was still
typing my original post... But I decided to post the question anyway...
so I could answer it. That way, the next poor schmoe (like myself) who
comes along with this problem will not have to suffer through the same
torture of reading thread after thread of people asking this question
with no one offering a solution. :op

So here it is... if you want to copy data from a table in one database
to an identical table in another database, or if you want to copy data
from a datatable into a similar table in a datatbase (other than the
one you got it from)... then this is your lucky day. :oP

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
CommandBuilder
'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
dt = New DataTable
da1.Fill(dt)
da1.Dispose()
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
statements
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
da2.Update(dt)
da2.Dispose()
cmd.Dispose()
dt.Dispose()
End If
Next



WATYF
 
Hi,

If you don't want to use complete SQL commands to copy the tables, than try
to do this using the datareader and the "INSERT" in the backup tables.

A dataset gives you in my opinion only overhead in this case.

Cor
 
Actually, there are a couple alternatives... for example, if you're
using the Jet engine, you can use SELECT INTO IN or INSERT INTO IN
statements, like so:

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

or

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

....but the db I'm working with doesn't support that syntax.


I don't quite follow what you mean by using the datareader and the
INSERT statement, though.


WATYF
 
An even better solution is to use BCP or DTS to move the data.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
This isn't for SQL Server, it's for a 3rd party embedded database...
and I was looking for a fully programmatic, no-administration-needed
solution.



WATYF
 
Back
Top