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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CodeCruiserConnect With a Mentor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
mustang61Author Commented:
Worked to perfection - THANKS!
0
 
CodeCruiserCommented:
Glad to help :-)
0
All Courses

From novice to tech pro — start learning today.