Transfering data from One Sql Server to Another

Posted on 2004-11-12
Medium Priority
Last Modified: 2010-05-02
Select * into Database1.tempdb.DBO.rohantemp from Database2.Dbo.Table1

I want to transfer data from one SQL server tro another both are on same n/w and reachable from one another.

please help

this is my connection string
Set conn = CreateObject("ADODB.Connection")
'Opening Connection
conn.Open ("Driver=SQL Server;Server=" + InServer + ";UID=usr;PWD=pwd;Database=" + InDbase)


Question by:rohanbairat3
  • 2
LVL 12

Accepted Solution

pique_tech earned 1000 total points
ID: 12569625
The easiest way to do this is using the SQL DTS tool.  If you can use that and are interested in more information, post here.  You do have to have the SQL Server Client Tools installed on your workstation to use this path.

The easiest way to do this using ADO is to have Server2 added as a linked server to Server1, because that would only require ONE ADO connection.  However, if you cannot do that, you will need separate ADO connections to each server, then you'll have to retrieve the data as a recordset from Server1 and then append it, record by record and field by field, to Server2.

How do you want to proceed?

Author Comment

ID: 12570074
A link would be great ... i know we can create a DBLink in oracle. I dont know about SQL server ... can I create that link on the fly and delete it after my program executess ?

I dont want to use DTS tool...or record set ...

LVL 12

Expert Comment

ID: 12570225
If you don't want to use DTS or a recordset, then I don't know how I can help.  I'm not familiar with the DBLink utilized in Oracle--my experience with Oracle is quite limited, though I'm pretty adept at all the Microsoft data access and manipulation tools.  It sounds like it could be like a Linked Table, but that's only available in Microsoft Access, and you haven't mentioned anything about using Access.

The connection code snippet you provided in your original question is the ADO method for connecting to a database.  I assumed that you'd be using ADO to accomplish your task, and the only ADO way I know to do this would be with recordsets.
LVL 10

Assisted Solution

PSSUser earned 1000 total points
ID: 12602685
Is it just the DTS tool you don't want to use? You could use the DTS object library (Microsoft DTSPackage Object Library) from VB. I've used this from code to copy data from an access database on an install CD into a SQL server database. The code is currently written in PASCAL (I use INNO install) but if you're interested I'll convert it into VB.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question