sql-server - INSERT 和 UPDATE 如何根据 IF 条件工作？

Question

很想知道 SQL Server 中的一个简单 SQL 概念。我有两个表，基于某些 IF 条件，我想更新和插入来自不同表的数据。我在存储过程中使用下面的查询。

IF( SELECT COUNT(colname) FROM [dbo].[Table_BKP] WHERE colname LIKE ('%Dear(DEC''D)444-0292/555-4528C%'))> 0 
BEGIN   
 ;WITH Dear AS 
(
SELECT ID,colname,SUBSTRING(colname,PATINDEX('%(DEC''D)%',colname),LEN(colname)-1) AS colname_4SUPPL,LEFT(colname,PATINDEX('%(DEC''D)%',colname)-1) AS colname_4MAIN
FROM [dbo].[Table_BKP] WHERE RTRIM(LTRIM(colname)) LIKE ('Dear(DEC''D)444-0292/555-4528C')
) SELECT * INTO Dear_ FROM Dear

 UPDATE A SET A.colname = B.colname_4MAIN FROM Table AS A INNER JOIN 
  Dear_ AS B ON A.ID = B.ID 
  INSERT INTO Table_SUPPL(ID,colnameMSC,[SOURCE],[TARGET])
 SELECT ID,replace(colname_4SUPPL,'(DEC''D)',''),'colname','colnameMSC' 
 FROM Dear_ 
END

更新语句工作正常，数据正在更新到所需的表中。但是，数据没有插入到所需的表中。

但是，当我改变如下顺序时，它工作得很好。

IF( SELECT COUNT(colname) FROM [dbo].[Table_BKP] WHERE colname LIKE ('%Dear(DEC''D)444-0292/555-4528C%'))> 0 
     BEGIN  
   ;WITH Dear AS 
(
SELECT ID,colname,SUBSTRING(colname,PATINDEX('%(DEC''D)%',colname),LEN(colname)-1) AS colname_4SUPPL,LEFT(colname,PATINDEX('%(DEC''D)%',colname)-1) AS colname_4MAIN
FROM [dbo].[Table_BKP] WHERE RTRIM(LTRIM(colname)) LIKE ('Dear(DEC''D)444-0292/555-4528C')
) SELECT * INTO Dear_ FROM Dear

   INSERT INTO Table_SUPPL(ID,colnameMSC,[SOURCE],[TARGET]) SELECT 
   ID,replace(colname_4SUPPL,'(DEC''D)',''),'colname','colnameMSC' FROM 
  Dear_ 
  UPDATE A SET A.colname = B.colname_4MAIN FROM Table AS A INNER JOIN 
Dear_ AS B ON A.ID = B.ID 

END

为什么，formar 查询没有按预期工作？并且，后一个[只是在更改序列之后（第一次插入然后更新）]；它工作得很好...... SQL Server 如何管理这些序列？谁能澄清这个简单的疑问？谢谢。

标签： sql-serversql-server-2016