How to perform cross server insert in SQL Server?

Question

I have 3 tables in 2 servers.

1. Table A in server A
2. Table B in Server B
3. Table C in Server B

Server A and Server B are linked.

Table A:

Fruit   Quantity   Total Price
Apple     2            4 
Banana    4           12
Orange    6           24

Table B:

Fruit    Unit Price 
Apple        2
Banana       3
Orange       4

Table C:

Fruit   Quantity    Unit Price   Total Price
Apple     2             2             4 
Banana    4             3            12
Orange    6             4            24

I'm required to fetch data from Table A to Table C. I also need to refer to Table B to get the Unit Price which is needed for Table C. What I did is:

insert into [Server B].[DatabaseName].[SchemaName].[Table C]
select 
Fruit, Quantity, '', Total Price 
from table A

But I am still unable to get the Unit Price of fruit. May I know what kind of query can I use to get the Unit Price and put into the select query above?

Answer 1

    with temp as 
    (
    select 
    A.Fruit, A.Quantity, B.[Unit Price], A.[Total Price] 
    from [Server A].[DatabaseName].[SchemaName].[Table A]
    inner join [Server B].[DatabaseName].[SchemaName].[Table B] on A.Fruit=B.Fruit
    )

    insert into [Server B].[DatabaseName].[SchemaName].[Table C]
    select A.Fruit, A.Quantity, B.[UnitPrice], A.[Total Price] from temp

---Or---

    insert into [Server B].[DatabaseName].[SchemaName].[Table C]
    select   A.Fruit, A.Quantity, B.[Unit Price], A.[Total Price] 
    from [Server A].[DatabaseName].[SchemaName].[Table A]
    inner join [Server B].[DatabaseName].[SchemaName].[Table B] on A.Fruit=B.Fruit

Of course, You can do it using Linked Server sp_addlinkedserver

I suppose you've already set it up