Inserting from one db into another

[John]

Hi

I have open connection to two separate databases. I now need to insert
records from a table in one db into a table in second db. How can I go about
doing it?

Thanks

Regards

 

[RobinS]

Read from one, write to the other.

Robin S.

 

[Guest]

Hi John?

no need use two connection

the simple :
SqlCon.Open()
SqlDataAdapter.fill(Dataset1)
SqlCon.ChangeDatabase("DestinationDatabase")
SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value)
SqlDataAdapter.InsertCommand.ExecuteNonQuery

actually im not use code like that. ( it's simple for you to understands)

 

[Guest]

Read from one, write to the other.

of course RobinS!

'// first it's source database you want to read.
SqlCon.Open()

'// you already get the data and fill to dataset
SqlDataAdapter.fill(Dataset1)

'// Then Change database destination to database you want to write.
SqlCon.ChangeDatabase("DestinationDatabase")

'// Insert Now.
SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value)
SqlDataAdapter.InsertCommand.ExecuteNonQuery

It's really works

 

[John]

Thanks.

Regards

 

[John]

Will something like below also work;

LocalConn.Open()

Dim DBCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders
SELECT * FROM [Orders] IN ''[ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes];'',
LocalConn)

DBCommand.ExecuteNonQuery()

LocalConn.Close()

Thanks

Regards

 

[William \(Bill\) Vaughn]

If the target database is SQL Server you do NOT want to simply perform
INSERT statements unless you're being paid by the length of time it takes to
run your program.

ADO and all of the other data access interfaces are not designed to move
data from server-to-server. Use DTS, BCP or better yet ADO.NET 2.0's
SqlBulkCopy API.

hth

--
____________________________________
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Paul Clement]

¤ Will something like below also work;
¤
¤ LocalConn.Open()
¤
¤ Dim DBCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders
¤ SELECT * FROM [Orders] IN ''[ODBC;Driver={SQL
¤ Server};Server=(local);Database=Northwind;Trusted_Connection=yes];'',
¤ LocalConn)
¤
¤ DBCommand.ExecuteNonQuery()
¤
¤ LocalConn.Close()

Yes it will work as long as the columns map properly in each table. Otherwise, the column names may
need to be specified in the SQL statement.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[John]

Hi Bill

It is just a few records 10-20 at a time. I just need an easy way to run
insert and update queries between the two dbs.

Thanks

Regards

 

[Guest]

Dim DBCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders

SELECT * FROM [Orders] IN ''[ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes];'',
LocalConn)

it's really works, but ...
assume you got a lot insert, update and delete method like above in multiple
Classes.

each query need specify 'Server' and other properties connection.

In fact you want to move connection to another server. Server=(local) ???
you need change all you code! ( OOP = is useless)

Code readability is needed.

hope this can help too

 

[Guest]

Well William ...

ADO and all of the other data access interfaces are not designed to move
data from server-to-server. Use DTS, BCP or better yet ADO.NET 2.0's
SqlBulkCopy API.

yes you're right!
but remember!
with feature you said above need more than lot process and multiple click

with Executeable Program at least move that record just with 3 mouse clicks

 

[Jim]

Denny Lim,

If the table in the Source Database field names are FieldA and FieldB
and the "DestinationDatabase" has fields are Field1 and Field2, what
would the SQL Query + (Dataset value) be in your example below?

Jim