Dynamically join data from different databases

• Red27

  SSC Veteran

  Points: 234

  More actions

  November 16, 2017 at 9:24 am

  #340553

  Hi all, I have a bit of a dilemma. I have two databases which contain customer data. DB1- contains Customer information. DB-2- wholesale product vendors. 

  I want to pull the whole sale vendors address into the Customer Information database dynamically without hard coding in the database name each time. For example:

  Create Table [DBO].[Customer]
  (
  Firstname NVARCHAR(50),
  lastname NVARCHAR(50),
  address_1 NVARCHAR(50),
  address_2 NVARCHAR(50),
  address_3 NVARCHAR(50),
  address_4 NVARCHAR(50),
  Companyname
  )

  Insert into [Customer] (
  Firstname,
  lastname,
  Address_1
  address_2,
  address_3,
  address_4,
  )

  select
  C. First as Firstname,
  C. last as lastname,
  C. Address1 as Address_1,
  C. Address2 as Address_2,
  C. Address3 as Address_3,
  C. Address4 as Address_4
  )
  FROM [Customer]
  Insert into Customer (
  Companyname
  address_1
  address_2,
  address_3,
  address_4,
  )

  select
  C. Company as Company,
  C. Address1 as Address_1,
  C. Address2 as Address_2,
  C. Address3 as Address_3,
  C. Address4 as Address_4
  )
  FROM [WholeSellerAddresses].[dbo].[CompanyAddress];

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  November 16, 2017 at 9:45 am

  #1968396

  I think that you are looking to CREATE SYNONYM.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA