Linking data from a different database

[nonamepub]

Hello All,

I've been a member of Webdeveloper.com, but I'm finding more and more of my solutions coming from this community, so here I am!

I'm currently looking for a way to import data from a remote database into my SQL 2005 DB. Here is the scenario: I have a DB with tables X, Y, and Z. Within tables X and Y, there are some fields which I want to populate from a remote database that already contains data for those fields.

Here's some kickers - I need the information to remain current at all times in my DB. Thus, doing manual dumps from the remote database and then importing manually is not ideal.

I have looked into Linked Servers, but this feature is somewhat different than what I need. I cannot obtain write access to the remote database. I only need to bring in the data. I do not need to manipulate it. If Linked Databases can accomplish this for me, please let me know, but I'm currently under the impression that it requires RW access.

Thanks for any help!

[rmiao]

Not true, you can get data via linked server with 'insert into ... select ... from ...' statement.

[nonamepub]

Really? That's good news. I'll go through the process of setting up the linked server to test it out, but you're suggesting I can perform an insert such as

insert into MyTable values (Select foo from linkedServer.ThatTable)

But if this is the case, will MY database know to keep this information up-to-date with the data on the linked server? If the answer is yes, then what would happen if I modified that entry that I inserted? It's my understanding that it would modify the linked data too, thus requiring write-access to the linked DB.

[rmiao]

But you said this in your original post:

I only need to bring in the data. I do not need to manipulate it.

[nonamepub]

Well, yes, the data shouldn't be modified once it's pulled in, but I'm asking "What if" I attempted to modify the data... Would the changes be reflected in the remote DB, or does the link replicate the other DBs' entries within my DB (thus, if I modified entry x, it would only be reflected in my system).

The later choice doesn't seem likely, but it's crossed my mind, so I thought I'd ask.

[rmiao]

When you change data on local server, it will not reflect to linked server. If you like to keep dbs on different servers in sync, you can set replication.

[nonamepub]

ok, great. Thanks for the insight!