How to Select from one sql database and insert into another database.?

Question

Post reply

How to Select from one sql database and insert into another database.?

sharma-691993

SSCrazy

Points: 2194
More actions
May 14, 2008 at 9:51 pm

#187688

Hi how to Select from one sql database and insert into another database.
Source db as Sql Server 2000
Target db as Sql Server 2005.
I need to select one table data from Server 2000 & insert into Sql Server 2005

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sreenivas Rao SSC Enthusiast Points: 112 More actions · Answer 1

Hi

In sql 2000 go to Master database add sp_addlinkedserver of sql 2005 & then follow the steps

Connect to sql 2000 , to the database & write the query

insert into xyz(a,b,c) select a,b,c from sql2005.sa.abc

where xyz is a blank table in sql 2000 , sql2005 is the name of the database in sql 2005 ,sa is the user who the right to the abc table & abc is the table

Regards

Sreenivas:)

sharma-691993 SSCrazy Points: 2194 More actions · Answer 2

Thanks for ur reply..

Hi target db is in sql server 2005.

Where i have to add sp_addlinkedserver in 2005 or 2000?

Regards

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

You can use the Import/Export wizard in SQL Server 2005, or - you can create an SSIS (Integration Services) package. If you use Import/Export wizard and save the package it will create an SSIS package for you that you can start with.

And finally, you can create a linked server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 4

The server that you are running the query on and inserting the data into - if it can see the other server or the source server.

On your 2005 server - create a linked server to the SQL 2000 server.

You can also use Management Studtio front-end under Server Objects --> Linked Servers

Thanks

Kevin

sharma-691993 SSCrazy Points: 2194 More actions · Answer 5

Thanks Kevin..

I am running the query in SQL 2005 only..

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 6

Have you linked the servers and are you able to return data from the SQL 2000 server?

Sreenivas Rao SSC Enthusiast Points: 112 More actions · Answer 7

Hi

Add sp_addlinkedserver in sql 2005

Regards

Sreenivas

Madhusudana Rao SSC Veteran Points: 269 More actions · Answer 8

Hi,

You can use openrowset statement without linking any server. If you are using this statement in 2005, you have configure 'Ad Hoc Distributed Queries' to 1.

sharma-691993 SSCrazy Points: 2194 More actions · Answer 9

Hi..

How to use this OPENQUERY without registering linked server?

Bob Pinella SSCommitted Points: 1737 More actions · Answer 10

Hi,

You have to register the linked server in order to use OPENQUERY. Here is a simple example for the use of OPENQUERY:

Select

X.FieldA,

X.FieldB,

X.FieldC

From

OPENQUERY([LinkedServerName],

'Select

FieldA,

FieldB,

FieldC

From

YourRemoteTable')

Bob Pinella

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 11

Why not create a DTS package if it is a one time job? Its very simple to transfer data (a table or more) from 2000 to 2005.

/Gosta