Search code examples
sql-serverstored-procedureslinked-serveropenquery

SQL Server stored procedure insert into table from linked server and update another table with max ID

I have a stored procedure that queries a linked server and performs inserts into a local table. I would like to store the max(RID) from the data that I have inserted so that I can use that RID from my tableL as a pointer for my next run.

I cannot use SCOPE_IDENTITY() as I'm not planning to store the identity column of my local but I would like to store max(RID) from the linked table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[procedure_name]

    DECLARE @RID INT 
AS
BEGIN
   INSERT INTO tableA (RID, Name)
       SELECT * 
       FROM OPENQUERY(linkname, 'SELECT RID, Name FROM linktable WHERE RID > @RID')
END

TableA (ID is sequential PK)

    | ID | RID | Name |
    |----|-----|------|

TableL (ID is sequential PK)

    | ID | RID | Time |
    |----|-----|------|
Solution

  • Note: I realized after posting this that you can't pass variables into OPENQUERY, so I changed it to just be a direct query. Depending on your usage, this may or may not be okay.

    I'm personally a fan of temp tables, so I would probably do something like this:

    -- Get the RID of the last successful run for this table
DECLARE @LastRID int = 0;
SELECT @LastRID = LastRID
FROM dbo.SomeETLTrackingTable
WHERE TableName = 'linktable';

-- Pull the data from linkname over to this server
SELECT RID, [Name]
INTO #data
FROM linkname.linkdb.dbo.linktable
WHERE RID > @LastRID;

-- Insert the data we pulled
INSERT INTO tableA ([Name])
SELECT [Name]
FROM #data;

-- Get the max RID of the batch
SELECT @LastRID = MAX(RID)
FROM #data;

-- Update our ETL tracking table
UPDATE e SET e.LastRID = @LastRID
FROM dbo.SomeETLTrackingTable e
WHERE e.TableName = 'linktable';

    Note: I'm not including any transactions or error handling, but you should to ensure you only update the tracking table upon successful import.

    I would also suggest you learn other methods for ETL'ing data. I'm not going to say that what you're doing is wrong, because it works perfectly fine for many applications. But if you are trying to do more complex ETL processes in the future, it would be beneficial to learn what other options you have.

    I'm only going to name a couple because there are TONS of things out there: