Transfering data from One Sql Server to Another

Posted on 2004-11-12
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
    LVL 12

    Accepted Solution

    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?
    LVL 7

    Author Comment

    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

    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

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now