比對並Update的SQL - MS SQL version

系統上線一陣子後因為資料日漸增多，我們會把資料切成online, offline的部分，有時會想將online更新到offline，這時我們把兩個Table的資料做比對，找出相同的資料後，更新offline。

使用下面的SQL可以更新offline資料。使用cursor。

DECLARE c Cursor FOR
  select pd.tankid, pd.bodid, pd.sampletstamp
  from newbod.dbo.tblQCThickMeasurements bod, ProcessData.dbo.tblQCThickMeasurements pd
  where bod.tankid = pd.tankid
  and bod.bodid = pd.bodid
  and bod.sampletstamp = pd.sampletstamp
  and pd.qualcopied = 0;
Open c;
IF (@@CURSOR_ROWS<>0) ---判斷cursor開啟狀況
 BEGIN  
  DECLARE @tankId VARCHAR(50), @BodId VARCHAR(50), @sampleTS Datetime;  
  Fetch NEXT FROM c INTO @tankId, @BodId, @sampleTS;

  While (@@FETCH_STATUS <> -1)--當陳述式失敗，或資料列超出結果集時停止迴圈
   BEGIN
    update ProcessData.dbo.tblQCThickMeasurements set
      qualcopied=1 
    where tankid = @tankId
    and bodid = @BodId
    and sampletstamp = @sampleTS;
    Fetch NEXT FROM c INTO @tankId, @BodId, @sampleTS;
   END;
 END;
CLOSE c;
DEALLOCATE c;
GO