VB.NET tabledapter query insert into from another dataset

Question

I have a situation where I want to Insert into access DB table from MS SQL table. Same columns and everything. I have both data sets and both table adapter. I can do what ever I want inside each dataset - any manipulation but I cannot insert from one table to another. I tried creating an Insert query for destination tableadapter but I cannot get the from working. Tried linking, nothing works.

Searched for days, simply cannot find it.

Thank you for your answer. Can you help me on my example. I'm having trouble setting this up. This is what i got:

Dim myToTableTableAdapter As FirstDataSetTableAdapters.ToTableTableAdapter
myToTableTableAdapter = New FirstDataSetTableAdapters.ToTableTableAdapter()
Dim myFromTableTableAdapter As SecondDataSetTableAdapters.FromTableTableAdapter       
myFromTableTableAdapter =  New SecondDataSetTableAdapters.FromTableTableAdapter()

myFromTableTableAdapter = myToTableTableAdapter.Clone
'but it doesnt work from here`

What I wanted to do is:

For each drfrom As DataRow In myFromTableTableAdapter.GetData

myToTableTableAdapter.InsertInto(drfrom.item(column01), drfrom.item(column02), drfrom.item(andSoOn))

Next

But it seem to me that this would take so much longer then a "Insert Into From Select" script.

Jim Black · Answer 1 · 2015-08-25T22:48:09.053

0

You cannot insert a row from one table into another table, but there are a couple of ways to do what you want. One way (a little verbose) is this:

' sets it up with same schema but empty rows
 mOutTable = inTable.Clone  

'  Now insert the rows:
 For Each rowIn In inTable.Rows    
     r = mOutTable.NewRow()    
     For Each col In inTable.Columns
          r(col.ColumnName) = rowIn(col.ColumnName)    
     Next    
     mOutTable.Rows.Add(r) 
Next
mOutTable.AcceptChanges

A second way, which is more concise, is this:

  outTable = inTable.Clone  
  For Each inRow As DataRow In inTable.Rows
        outTable.LoadDataRow(inRow.ItemArray, False)
  End If

  outTable.AcceptChanges

Note that both inTable and outTable are ADO.NET DataTable objects. You cannot implement my suggestion on the DataAdapter objects. You must use the DataTable objects. Each DataTable can be associated with a DataAdapter in the standard fashion for ADO.NET:

 Dim t as New DataTable()
 a.Fill(t);

where a is the ADO.NET DataAdapter. I hope this helps! Jim

edited Aug 25 '15 at 22:48

answered Aug 24 '15 at 14:42

Jim Black

109
1
6

Thanks for the answer, I've updated my question so it is more understandable. – DAMS Aug 24 '15 at 19:35
OK, I see. The solution I proposed requires that you get access to the DataTable associated with each DataAdapter. The standard way of doing this goes something like this – Jim Black Aug 25 '15 at 22:43
My previous comment was incomplete. I meant to say "The standard way of doing this is to create a DataTable and fill it from the DataAdapter. I have shown the ADO.Net code fragment for doing this at the end of my original answer". Hope this helps! – Jim Black Aug 25 '15 at 22:56

VB.NET tabledapter query insert into from another dataset

1 Answers1