CODEWITHSUNDEEP
sqlsql-serverlinked-server
Karl
Karl

Reputation: 65

How to perform cross server insert in SQL Server?

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?

Upvotes: 1

Views: 1256

Answers (3)

Hesam Akbari
Hesam Akbari

Reputation: 1141

    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

Upvotes: 0

moinmaroofi
moinmaroofi

Reputation: 215

This query may work for you.

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

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37039

To be complete with the answer, you could do this:

insert into [Server B].[DatabaseName].[SchemaName].[Table C]
select a.Fruit, a.Quantity, b.[unit price], a.[Total Price]
from table A
join [Server B].[DatabaseName].[SchemaName].[Table B] B on A.fruit = B.fruit

Explanation

You join your table a with table b so you can get unit price from b and rest of the data from a. That can be used to insert data into c

Upvotes: 2

Related Questions