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

7 Nov 2005 4:13 AM

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

7 Nov 2005 4:41 AM

WATYF1

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

7 Nov 2005 6:42 AM

Cor Ligthert [MVP]

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

7 Nov 2005 3:20 PM

WATYF1

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

7 Nov 2005 4:31 PM

William (Bill) Vaughn

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.
__________________________________

<WAT***@gmail.com> wrote in message

Show quote

news:1131336811.596776.78310@o13g2000cwo.googlegroups.com...
>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
>

7 Nov 2005 5:01 PM

WATYF1

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