Select * from table A for all entries in table B with Linked Server

[FC2003]

Hi,

I'm trying to populate table A base on all records contained in table B from table C wich is on a linked server

for example

Table B on server1
a
b
c
d
e

Table C on server2
a 1
a 2
b 2
c 4
d 3
d 5
e 3
f 2
g 1

Will result in Table A on server1

a 1
a 2
b 2
c 4
d 3
d 5
e 3

In another language we have the command : for all entries
like this

Select * from table c into table a
for all entries in table b where table c.field1=table b field1

Is this possible in SQL, with a DTS I guess.

Thanks

 

[maswien]

Use four part name:

select * into server1.dbname.dbo.tableA
from
select C.*
from server1.dbname.dbo.tableB as B
inner join server2.dbname.dbo.tableC as C
on B.col1 = C.col1

 

[maswien]

opps, it's wrong!

it should be:

select C.* into server1.dbname.dbo.tableA
from server1.dbname.dbo.tableB as B
inner join server2.dbname.dbo.tableC as C
on B.col1 = C.col1

 

[FC2003]

Thanks mjia,

it did works fine.