Question Ideas on processing more than 1 database/table

[cbrown747]

I am trying to read a table from database A and insert the data to another Table in Database B on the same server. I want to do this within the vb.net code.

 

[kulrom]

Usually i do it like following:

-- create a copy of the old table structure (optional)
Select * Into NewTable From OldTable Where 1 = 2;

-- insert data from table A into table B
Insert Into NewTable (field1, field2, field3) 
Select field1, field2, field3 From OldTable

 

[cbrown747]

thanks for the reply.
My main confusion is setting it up to read both tables. My understanding is you can only apply one connection to one database so I would need two connections, correct?
Then I would read one table and then do a find or whatever to the matching record into the database I am trying to either add or update too.

 

[kulrom]

Actually you can do that in a single query e.g.

Insert Into 
     DBName.SchemaName.NewTableName 
     (field1, field2, field3) 
Select 
     field1, field2, field3 
FROM 
     DBName.SchemaName.OldTableName
WHERE
    [Condition Here]

SchemaName usually is [dbo]

 

[cjard]

I'd definitely advocate following kulrom's advice here, even to the point of: if you had to download data from e.g. a SQL Server into e.g. an Access database, then attaching the Access database to the SQL server and using an SQL statement to push the data directly is the fastest way..

Always avoid downloading data into your client program if you can. The only times you might have to do this are when you have data in some text files, or other format not easily supported by your database. If the data is already in a database, look for any way you can of getting the data to the destination directly

 

[cbrown747]

thanks for the responses. I was actually simply looking for sample vb code that works with the sql database. In other words something like the following:

1. get the connection
2. setup sql statement (select * from table)
3. loop through table A
4. Get corresponding record from Table B
5. Conditional statement
If record found
then update
else
add record to Table B\
end if

The issue is how to read through the primary table
A and then next how to find the record in Table B if there is one before I do the conditional statement.

 

[cjard]

But you didnt state what the database in use was and you have changed the premise of your question. What was a simple insert from a->B is now an "insert if not existing, update if existing". If we knew the database we could comment whether the MERGE statement is available (ORACLE 9+, SQLSERVER 2008+) which will make your life a lot easier

To find out how to download data from your database to your local app, read the DW2 link in my signature Creating a Simple Data App

Once you have completed that tutorial you'll have all the means/knowledge necessary to download data,alter it,upload it. It would go something like:

tableAdapterA.Fill(dataTableA)
tableAdapterB.Fill(dataTableB)
For Each ro as ARow in DataTableA
  bro as DataTableBRow = dataTableB.FindByID(ro.ID);
  If bro Is Nothing Then
    dataTableB.AddBRow(ro.value, ro.value, ro.value...)
  Else
    bro.ID = ro.ID
    bro.value = ro.value
    ...
  End If
Next
tableAdapterB.Update(dataTableB) 'update means "save", it performs INSERT as well as UPDATE

These terms will only make sense after you do the tutorial. If you don't call your tables A and B then your names will vary