insert data

Question

Post reply

insert data

shanila_minnu

Hall of Fame

Points: 3465
More actions
June 18, 2009 at 3:56 am

#138803

how can we insert rows from a table XYZ of one database to another database table XYZ

Viewing 6 posts - 1 through 5 (of 5 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

shanila_minnu (6/18/2009)

how can we insert rows from a table XYZ of one database to another database table XYZ[/qote]

insert into table1(col1,col2,col3)

select col1, col2, col3 from server2.database2.owner2.table2

You'll need to create linked server before doing that.

use sp_linkedserver 'other_server_name'

Pradeep Singh

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 2

You could also use OpenRowSet without creating a linked server, I believe. Or SSIS.

There are plenty of methods available. The question is, what tools does your workplace have available for your use?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

ps. One Orange Chip Points: 29422 More actions · Answer 3

heh... there is no need to create linked server... OP wants data to access from other database and not other server.

This should do fine if db is present in same server..

insert into table1(col1,col2,col3)

select col1, col2, col3 from database2.owner2.table2

or as Brandy said, openrowset query...

Pradeep Singh

Dugi SSCoach Points: 17998 More actions · Answer 4

...or you can do Import/Export from one database to another database!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Russel Bell Old Hand Points: 305 More actions · Answer 5

ps (6/19/2009)
heh... there is no need to create linked server... OP wants data to access from other database and not other server.
This should do fine if db is present in same server..
insert into table1(col1,col2,col3)
select col1, col2, col3 from database2.owner2.table2
or as Brandy said, openrowset query...

Link server is only necessary if the database is on a different server. If the database is on the same server, the 3-level naming is sufficient.

😛