Step1-LinkDB.sql

--Step 1 Link to the Database
--More info: http://jeffspiller.net/2016/01/moving-data-with-linked-databases.html
--Copyright 2016 Jeff Spiller. All rights reserved.
--Licensed under the MIT License -- License Details at  https://opensource.org/licenses/MIT

Use master

if 
(select count(name) from  sys.servers where name='AzureTest' ) =1   
begin

	EXEC dbo.sp_dropserver @server=N'AzureTest', @droplogins='droplogins'

end


EXEC sp_addlinkedserver
@server='AzureTest', -- here you can specify the name of the linked server
@srvproduct='',     
@provider='sqlncli', -- using SQL Server Native Client
@datasrc='somename.database.windows.net',   -- add here your server name
@catalog='AzureTest12'

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AzureTest',
@useself = 'false',
@rmtuser = 'SomeUser',             -- add here your login on Azure DB
@rmtpassword = 'SomeName' -- add here your password on Azure DB

Step2-CreateTempTable.sql

--Step 2 Create the temp table
--More info: http://jeffspiller.net/2016/01/moving-data-with-linked-databases.html
--Copyright 2016 Jeff Spiller. All rights reserved.
--Licensed under the MIT License -- License Details at  https://opensource.org/licenses/MIT


use tempdb 
IF object_id('tempdb.dbo.tmpCustImport') is not null
begin
  truncate table tmpCustImport
  drop table tmpCustImport
 
end

create table tmpCustImport(
CustomerID int  
,Title nvarchar(8) 
,FirstName nvarchar(50) 
,MiddleName nvarchar(50)
,LastName nvarchar(50)
,Suffix nvarchar(10) 
,CompanyName nvarchar(128) 
,EmailAddress nvarchar(50)
,Phone nvarchar(25)
,AddressLine1 nvarchar(60) 
,AddressLine2 nvarchar(60) 
,City nvarchar(30) 
,StateProvince nvarchar (50) 
,PostalCode nvarchar(15) 
,ModifiedDate datetime
,RecExists bit  ) 

Step3 -RetrieveFromRemoteDB.sql

--Step 3 retreive data from linked remote database
--More info: http://jeffspiller.net/2016/01/moving-data-with-linked-databases.html
--Copyright 2016 Jeff Spiller. All rights reserved.
--Licensed under the MIT License -- License Details at  https://opensource.org/licenses/MIT



insert into tempdb.dbo.tmpCustImport 
SELECT 
Customer.CustomerID
,isnull(Customer.Title,'') Title
,Customer.FirstName
,isnull(Customer.MiddleName,'') MiddleName
,Customer.LastName
,isNull(Customer.Suffix,'') Suffix
,Customer.CompanyName
,Customer.EmailAddress
,Customer.Phone
,Address.AddressLine1
,isNull(Address.AddressLine2,'') AddressLine2
,Address.City
,Address.StateProvince
,Address.PostalCode
,Customer.ModifiedDate
,0 --- RecExists with default value of 0 for import step
FROM 
AzureTest.somename.SalesLT.Customer Customer  -- you most qualifiy it with the name you gave the linked db in step one and the actual database name
	join AzureTest.somename.SalesLT.CustomerAddress CustAddrXref on CustAddrXref.CustomerID=Customer.CustomerID
    join AzureTest.somename.SalesLT.Address Address  on CustAddrXref.AddressID=Address.AddressID
where 
CustAddrXref.AddressType='Main Office'
and Customer.ModifiedDate > dateAdd(YY,-10,getDate()) --since last time you queried the data



       

Step4-UpdateDB.sql

--Step 4 Insert/Update Local Table
--More info: http://jeffspiller.net/2016/01/moving-data-with-linked-databases.html
--Copyright 2016 Jeff Spiller. All rights reserved.
--Licensed under the MIT License -- License Details at  https://opensource.org/licenses/MIT



--Figure out what are existing records and update the recExits table  appropriately
USE [AdventureWorks2014]
update tempdb.dbo.tmpCustImport
set recExists= (select count(recid) from MailingListTable  where  MailingListTable.webCustomerID=customerID)



-- Insert New Records

GO

INSERT INTO [dbo].[MailingListTable]
           ([WebCustomerID]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[CompanyName]
           ,[Email]
           ,[Phone]
           ,[AddressLine1]
           ,[AddressLine2]
           ,[City]
           ,[StateProvince]
           ,[PostalCode]
           ,[Created]
           ,[Modified]
           ,[Source])
   
SELECT [CustomerID]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[CompanyName]
      ,[EmailAddress]
      ,[Phone]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvince]
      ,[PostalCode]
	  ,getDate()
	  ,getDate()
	  ,'Web Database Import'
    FROM [tempdb].dbo.[tmpCustImport]
	where recExists=0;

GO
USE [AdventureWorks2014]
--Update Exitsting Records
UPDATE [dbo].[MailingListTable]
   SET 
       [Title] = tmpCustImport.Title
      ,[FirstName] =tmpCustImport.firstName
      ,[MiddleName] =tmpCustImport.MiddleName
      ,[LastName] =tmpCustImport.LastName
      ,[Suffix] =tmpCustImport.Suffix
      ,[CompanyName] =tmpCustImport.CompanyName
      ,[Email] =tmpCustImport.EmailAddress
      ,[Phone] =tmpCustImport.Phone
      ,[AddressLine1] =tmpCustImport.AddressLine1
      ,[AddressLine2] =tmpCustImport.AddressLine2
      ,[City] =tmpCustImport.City
      ,[StateProvince] =tmpCustImport.StateProvince
      ,[PostalCode] = tmpCustImport.PostalCode
      ,[Modified] = getDate()
      ,[Source] = 'Web Database Import'

 from
 tempdb.dbo.tmpCustImport tmpCustImport
 WHERE 
 MailingListTable.webCustomerID=tmpCustImport.CustomerID
 and tmpCustImport.recExists=1
 
GO

Step5-UnlinkDB.sql

--Step 5 Unlink and Clean Up
--More info: http://jeffspiller.net/2016/01/moving-data-with-linked-databases.html
--Copyright 2016 Jeff Spiller. All rights reserved.
--Licensed under the MIT License -- License Details at  https://opensource.org/licenses/MIT


use master

IF object_id('tempdb.dbo.tmpCustImport') is not null
begin
  truncate table tmpCustImport
  drop table tmpCustImport
 
end
GO

EXEC dbo.sp_dropserver @server=N'AzureTest', @droplogins='droplogins'
GO