Transfering data from One Sql Server to Another

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)


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.

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?

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
rohanbairat3Author Commented:
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 ...

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.
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.
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
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.