Insert Into SQL Server from another SQL Server using VB 2008

Is it possible to use Visual Basic to copy data from a sql server to a remote sql server?

If so what is the easiest way to do it?
mustang61Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try the steps below:

1. Establish two connection strings and connect to both servers
2. Fetch records from one Server
2a. If records are huge, dump records into a flat file and then insert from that temp file to another server.
3. Insert into another server.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mustang61Author Commented:
I created the 2 connections - i am a vb beginner - is the code easy to write to insert the table from one to another on different servers?
0
CodeCruiserCommented:
Code is easy but its just the performance side which you need to consider.

Open connection to server1
open connection to server2
use a command and datareader to read server 1

While DataReader.Read
     command2.commandtext = "Insert into ..."
     command2.executenonquery
End While
0
mustang61Author Commented:
these tables are not large and once the initial copy is done it will be only addons based upon on yes/no checlbox
0
CodeCruiserCommented:
Then the manual row by row copy should be fine
0
mustang61Author Commented:
the example is written in 'C' - not good at converting
0
CodeCruiserCommented:
Which example?



dim dbcon1 as new sqlconnection(connectionstring)
dbcon1.Open
dim dbcmd1 as new sqlcommand
dbcmd1.connection = dbcon1
dbcmd1.commandtext = "Select ..."
dim dbreader as sqldatareader = dbcmd1.executereader

dim dbcon2 as new sqlconnection(targetconnectionstring)
dbcon2.open
dim dbcmd2 as new sqlcommand
dbcmd2.connection = dbcon2
While dbreader.read
   dbcmd2.commandtext = "Insert Into ... Values" & dbreader.item(0) ...
   dbcmd2.executenonquery
End While
dbreader.close
dbcmd1.dispose
dbcmd2.dispose
dbcon1.dispose
dbcon2.dispose

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mustang61Author Commented:
Worked to perfection - THANKS!
0
CodeCruiserCommented:
Glad to help :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.