locked
How can I get data from another server to insert my local server? RRS feed

  • Question

  • Greeting!

    I have 2 server:

    Server 1

    Server 2(Local)

    I had created the linked server in my Server2 (linked server- SERVER1). I was attempting to create a query to get the data from the Server1, if Server2 doesn't have the data as Server1 then insert the data into Server2. It was successful execute the data from Server1 but it doesn't insert into Server2 (Local). Please advice me.

    Here is my query: 

    SELECT * FROM OPENQUERY (SERVER1, 'SELECT  EmployeeID, EmployeeName, JoinDate, CAST([XMLContent] AS NVARCHAR(MAX)) FROM dbo.Computer.tblEmployee WHERE JoinDate >= ''2016-02-29''')

    IF NOT EXISTS 
    (SELECT * FROM OPENQUERY ([SERVER2], 'SELECT  EmployeeID, EmployeeName, JoinDate, CAST([XMLContent] FROM dbo.Computer.tblEmployee'))

     

    INSERT INTO Computer.tblEmployee SELECT * FROM OPENQUERY ([SERVER2], 'SELECT  EmployeeID, EmployeeName, JoinDate, CAST([XMLContent] FROM dbo.Computer.tblEmployee')

     

    Wednesday, March 2, 2016 9:37 AM

Answers

  • The usage is not correct. You're just checking if atleast one record exists in linked server and only if none exists you're doing the insert which is wrong

    It should be this as per your requirement

    INSERT INTO Computer.tblEmployee 
    SELECT * 
    FROM OPENQUERY ([SERVER2], 'SELECT EmployeeID, EmployeeName, JoinDate, CAST([XMLContent] FROM dbo.Computer.tblEmployee') t WHERE NOT EXISTS (SELECT 1 FROM Computer.tblEmployee WHERE EmployeeID = t.EmployeeID)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 2, 2016 10:13 AM