SQL Server:从INSERT创建一个MERGE

Question

这就是我目前所拥有的。我需要做的是告诉它，如果FTPJobRunID在DAILY_JOBS中不存在，那么插入行，否则如果FTPJobRunID已经存在，则执行UPDATE。

-- Insert into Table the Statistics for the FTP Jobs
CREATE TABLE #TEMP (Filename VARCHAR(128), FTPJobRunID INT, FTPScheduledStart DATETIME, FTPJobStart DATETIME, FTPJobEnd DATETIME, FTPRerunAttempts INT)
INSERT INTO #TEMP 
   SELECT 
      RIGHT(LEFT(CAST(l3.params AS varchar(128)), CHARINDEX(',', l3.params)-1), LEN(LEFT(CAST(l3.params AS varchar(128)), CHARINDEX(',', l3.params)-1)) - CHARINDEX('=', LEFT(CAST(l3.params AS varchar(128)), CHARINDEX(',', l3.params)-3))) AS Filename,
        l1.id AS FTPJobRunID, l3.time AS FTPScheduledStart, l1.launchtm AS FTPJobStart, l1.stachgtm AS FTPJobEnd, l1.reruns AS FTPRerunAttempts FROM [server].[database].[dbo].[tablea] AS l1
   INNER JOIN 
       [server].[database].[dbo].[tablea] AS l2 ON l2.id = l1.prntid AND l1.type = 6
   INNER JOIN 
       [server].[database].[dbo].[tablea] AS l3 ON l3.id = l2.prntid 
   WHERE 
       l3.id IN (SELECT id 
                 FROM [server].[database].[dbo].[tablea]
                 INNER JOIN [server].[database].[dbo].[tableb] ON (tablea.owner = tableb.id)
                 WHERE jobrun_proddt = @ProdDate AND owner_name = 'blah' 
                   AND jobrun_cmd IS NULL AND jobrun_params IS NOT NULL);
-- Remove " from tail of odd Filenames
UPDATE #TEMP
        SET Filename = LEFT(Filename, LEN(Filename)-1)
    WHERE Filename LIKE '%"'
MERGE DAILY_JOBS AS TARGET
USING #TEMP AS SOURCE
????
DROP #TEMP

在KumarHarsh的一点帮助下，这是Merge down-所需的代码

MERGE DAILY_JOBS AS TARGET
USING #TEMP AS SOURCE
ON TARGET.FTPJobRunID = SOURCE.FTPJobRunID
WHEN NOT MATCHED THEN
INSERT (Filename, FTPJobRunID, FTPScheduledStart, FTPJobStart, FTPJobEnd, FTPRerunAttempts) values(source.Filename, source.FTPJobRunID, source.FTPScheduledStart, source.FTPJobStart, source.FTPJobEnd, source.FTPRerunAttempts)
WHEN MATCHED THEN
UPDATE
    SET Filename = source.Filename, FTPScheduledStart = source.FTPScheduledStart, FTPJobStart = source.FTPJobStart, FTPJobEnd = source.FTPJobEnd, FTPRerunAttempts = source.FTPRerunAttempts;
DROP TABLE #TEMP
END

Answer 1

试试这个，

MERGE DAILY_JOBS AS TARGET
USING #TEMP AS SOURCE
on TARGET.FTPJobRunID =SOURCE.FTPJobRunID
when not matched then
insert (col1,col2.....) values(source.col1,source.col2.......)
when matched then
update DAILY_JOBS set col1=source.col1............
where FTPJobRunID =SOURCE.FTPJobRunID