Хранимая процедура SQL Server вставляет в таблицу со связанного сервера и обновляет другую таблицу с максимальным идентификатором
У меня есть хранимая процедура, которая запрашивает связанный сервер и выполняет вставки в локальную таблицу. Я хотел бы сохранить max(RID) из данных, которые я вставил, чтобы я мог использовать этот RID из моей таблицы L в качестве указателя для моего следующего запуска.
Я не могу использовать SCOPE_IDENTITY(), так как я не планирую хранить столбец идентификаторов моего локального, но я хотел бы сохранить max(RID) из связанной таблицы.
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 — это последовательный ПК)
| ID | RID | Name |
|----|-----|------|
TableL (ID — это последовательный ПК)
| ID | RID | Time |
|----|-----|------|
Ответы 1
Примечание. После публикации я понял, что вы не можете передавать переменные в OPENQUERY, поэтому я изменил его на прямой запрос. В зависимости от вашего использования, это может быть или не быть в порядке.
Я лично поклонник временных таблиц, поэтому я бы, вероятно, сделал что-то вроде этого:
-- 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';
Примечание. Я не включаю какие-либо транзакции или обработку ошибок, но вы должны убедиться, что обновляете таблицу отслеживания только после успешного импорта.
Я бы также посоветовал вам изучить другие методы ETL-обработки данных. Я не собираюсь говорить, что то, что вы делаете, неправильно, потому что это прекрасно работает для многих приложений. Но если в будущем вы попытаетесь выполнить более сложные процессы ETL, было бы полезно узнать, какие еще варианты у вас есть.
Я назову только пару, потому что там МНОГО вещей:
rowversion- https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql- Отслеживание изменений — https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server
Спасибо, это помогает. Можете ли вы предложить какую-либо обработку ошибок, как это можно сделать, прежде чем я обновлю другую таблицу?
@RData рад, что это помогло. Если вы считаете, что это ответ на ваш вопрос, обязательно отметьте его как ответ. Что касается обработки ошибок, это слишком много, чтобы ответить в комментарии, я бы предложил присоединиться к слабому каналу сообщества SQL Server и каналам Reddit, чтобы попросить помощи по этому вопросу (brentozar.com/slack)
сделано и спасибо за предложение. И последний вопрос: если какой-либо из операторов (Insert или Update) в приведенной выше процедуре завершается ошибкой, останавливает ли это SP от выполнения любого из операторов? меня беспокоит то, что я не хочу обновляться, если какая-либо из моих вставок не работает
@RData, где бы он ни терпел неудачу, это то, что делается. Так что, если у вас есть вставка, а затем обновление, и обновление не удается, тогда вставка все равно пойдет. Чтобы избежать этого, вы должны использовать транзакции. Так что, если что-то не запустится, он может отменить любые ожидающие изменения. Но это определенно было бы неплохо спросить на слабом канале. Там гораздо более непринужденно, поэтому вы можете попросить о помощи, и они будут рады провести вас через это.
Похоже, вы хотели бы использовать предложение OUTPUT для захвата RID, которые вы вставляете, а затем где-то сохранять
max(RID). Кроме того, исходя из вашей логики, возможно,<=должно быть>, если вам нужны новые строки с момента последнего выполнения.