Inserting records from one database into identical table in another datrabase

[BillE]

Hi!
I'm transferring the records in a table from one database to another.

I create a data adapter using a connection from the first database to fill a
dataset.

I add an InsertCommand with a different connection to the second database,
and add the necessary parameters.

I hoped that when I called the update method, the records in the dataset
added from the first database would be inserted into the identical table in
the second database.

This didn't work, but no exception was thrown. Just no records were added.

My code is like this modified code from msdn - should this work?

Private Sub AdapterUpdate(ByVal connectionStringA As String, ByVal
connectionStringB As String)

dim connectionA As SqlConnection = New SqlConnection( _
connectionStringA)

dim connectionB As SqlConnection = New SqlConnection( _
connectionStringB)

Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connectionA)

adapter.InsertCommand = New SqlCommand( _
"INSERT Categories (CategoryName) values (@CategoryName),
connectionB)

adapter.UpdateCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")

Dim categoryTable As New DataTable
adapter.Fill(categoryTable)

adapter.Update(categoryTable)

End Sub

 

[Kerry Moorman]

BillE,

You might consider using the SqlBulkCopy class for this.

Kerry Moorman

 

[BillE]

Thanks for the suggestion, I'll check it out.

Is there a problem using the method I described?

Thanks
Bill

 

[Kerry Moorman]

Bill,

One problem is that there are not any new, changed or deleleted rows in the
datatable to update when you call the adapter's Update method.

Kerry Moorman

 

[BillE]

I see.

I tried creating a second dataset pulled from the second data adapter and
merging it with the first dataset, but it still doesn't appear to consider
the merged rows as 'New' rows, because they don't get inserted.

The only solution I have found is to loop through the original dataset and
Add the rows to the second dataset one by one - there doesn't appear to be
any other way to flag them as Inserted.

I'll take your suggestion and use the SQLBulkCopy class.

Thanks
Bill

 

[BillE]

I see examples in documentation using the SQLBulkCopyOperation class, but I
don't seem to have that class in SQLClient.
I do have the SQLBulkCopy class. I'm using .NET framework v2.0.50727.

 

[William Vaughn]

Except for the fact that it will be pitifully slow.
I would also investigate Linked servers or an SSIS script.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[BillE]

Plus it doesn't work.

Except for the fact that it will be pitifully slow.
I would also investigate Linked servers or an SSIS script.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________