Inserting records from one database into identical table in another datrabase

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

KerryMoorman
Wed Jan 30 17:01:00 CST 2008

BillE,

You might consider using the SqlBulkCopy class for this.

Kerry Moorman


"BillE" wrote:

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

BillE
Thu Jan 31 06:55:54 CST 2008

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

Is there a problem using the method I described?

Thanks
Bill


"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
> BillE,
>
> You might consider using the SqlBulkCopy class for this.
>
> Kerry Moorman
>
>
> "BillE" wrote:
>
>> 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
>>
>>
>>

KerryMoorman
Thu Jan 31 07:57:00 CST 2008

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" wrote:

> Thanks for the suggestion, I'll check it out.
>
> Is there a problem using the method I described?
>
> Thanks
> Bill
>
>
> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
> news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
> > BillE,
> >
> > You might consider using the SqlBulkCopy class for this.
> >
> > Kerry Moorman
> >
> >
> > "BillE" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>

BillE
Thu Jan 31 08:26:09 CST 2008

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

"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:B6C2890C-660E-4859-94B2-1A2C1D7EDBF0@microsoft.com...
> 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" wrote:
>
>> Thanks for the suggestion, I'll check it out.
>>
>> Is there a problem using the method I described?
>>
>> Thanks
>> Bill
>>
>>
>> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
>> news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
>> > BillE,
>> >
>> > You might consider using the SqlBulkCopy class for this.
>> >
>> > Kerry Moorman
>> >
>> >
>> > "BillE" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>>

BillE
Thu Jan 31 08:59:48 CST 2008

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.



"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
> BillE,
>
> You might consider using the SqlBulkCopy class for this.
>
> Kerry Moorman
>
>
> "BillE" wrote:
>
>> 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
>>
>>
>>

William
Thu Jan 31 13:12:41 CST 2008

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" <belgie@datamti.com> wrote in message
news:eQ4sfiAZIHA.5896@TK2MSFTNGP02.phx.gbl...
> Thanks for the suggestion, I'll check it out.
>
> Is there a problem using the method I described?
>
> Thanks
> Bill
>
>
> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
> news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
>> BillE,
>>
>> You might consider using the SqlBulkCopy class for this.
>>
>> Kerry Moorman
>>
>>
>> "BillE" wrote:
>>
>>> 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
>>>
>>>
>>>
>
>

BillE
Thu Jan 31 14:00:25 CST 2008

Plus it doesn't work.

"William Vaughn" <billvaNoSPAM@betav.com> wrote in message
news:D03846BA-6C7F-456D-A1C0-D586CB226D26@microsoft.com...
> 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" <belgie@datamti.com> wrote in message
> news:eQ4sfiAZIHA.5896@TK2MSFTNGP02.phx.gbl...
>> Thanks for the suggestion, I'll check it out.
>>
>> Is there a problem using the method I described?
>>
>> Thanks
>> Bill
>>
>>
>> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
>> news:B7FF1CEC-FE68-40AC-A45E-86B57023B478@microsoft.com...
>>> BillE,
>>>
>>> You might consider using the SqlBulkCopy class for this.
>>>
>>> Kerry Moorman
>>>
>>>
>>> "BillE" wrote:
>>>
>>>> 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
>>>>
>>>>
>>>>
>>
>>
>