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


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

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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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. p
>
>
> WATYF
>

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