move data from one Db to another DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

move data from one Db to another DB

Author

Topic

abhi900
Starting Member

2 Posts

Posted - 2011-08-10 : 00:16:41

1. I have a SQL statement which lists me all those 2000 record set,
2. Import these 2000 record set into another field of another table of another Database (all dB residing on the same server).
3. Current structure of the table where the data needs to be imported has 24 values in it as of now.
4. Need a SQL statement which wil import the 2000 records into this new field and subsequently that SQL statement also needs to add more values to the the remaining fields of the same table.
5. Other fields have a fixed value so we can specify those values in the Insert Statement.
6. I need a syntax on how to move the data (retrieved by SQL statement) into another database table.

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 01:56:42

Create a linked server and use OPENQUERY with insert statement.

PBUH

abhi900
Starting Member

2 Posts

Posted - 2011-08-10 : 08:24:52

both db are on the same server ..so cannot apply linked server and how does OpenQuery updates the field in question and subsequently other fields ?

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 08:43:05

You can create a local linked server.Google for it.

After creating the linked server you can use a query like this

[code]

use FirstDB
go
Insert into FirstTable(column)
Select * from openquery([LinkedServer],'select column from SecondDB.dbo.SecondTable')

[code]

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 09:05:13

if you've linked server setup you can simply use

Insert into FirstTable(column)
Select columns from [LinkedServer].SecondDB.dbo.SecondTable

ie use four part naming convention

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources