Question

我想作以下工作:当主要钥匙配对时,如果与活跃的Y号字不成,则插入记录。这是可能的吗?

我试图这样做:

-- Merge statement
MERGE INTO table1 AS DST
USING table2 AS SRC
ON (SRC.Code = DST.Code)

 --Existing records updated if data changes
WHEN MATCHED 
AND IF NOT EXISTS (WHERE active= Y  FROM table1 )

THEN
INSERT INTO table1 (colum)
SELECT value

+-------+-------------+--------+
| Code  | description | Active |
+-------+-------------+--------+
| AB    | just        |    |
|       |  something  | No     |
+-------+-------------+--------+

只有当同一部法典没有积极记录时,我才希望插入一个记录。新的记录希望能做到这一点。

+-------+-------------+--------+
| Code  | description | Active |
+-------+-------------+--------+
| AB    | something   |    |
|       | else        | YES    |
+-------+-------------+--------+

我希望这能够更明确地表明这一点。

edit: Never mind its not possible, I just got this error message: An action of type INSERT is not allowed in the WHEN MATCHED clause of a MERGE statement.

Answer 1

如果我正确理解,插入@T2的行文。页: 1

declare @T1 table
(
  Code char(2),
  Descr varchar(10),
  Active char(1)
)

declare @T2 table
(
  Code char(2),
  Descr varchar(10)
)

insert into @T1 values
( 1 ,  Desc 1 ,  y ),
( 2 ,  Desc 2 ,  n )

insert into @T2 values
( 1 ,  Desc 1 ),
( 2 ,  Desc 2 ),
( 3 ,  Desc 3 )

merge @T1 as D
using @T2 as S
on D.Code = S.Code and 
   D.Active =  y 
when not matched then
  insert (Code, Descr, Active) 
    values (Code, Descr,  y );

select *
from @T1

结果:

Code Descr      Active
---- ---------- ------
1    Desc 1     y
2    Desc 2     n
2    Desc 2     y
3    Desc 3     y

还将加入第3条。如果你不希望的话,就意味着你只想插入一行:@ T1 如果在<代码>@T2上已有一行,则在代码上有一行,但<代码>。活性 = n , 您可使用这一代。

merge @T1 as D
using (select Code,
              Descr
       from @T2
       where Code in (select Code 
                      from @T1 
                      where Active =  n )) as S
on D.Code = S.Code and 
   D.Active =  y 
when not matched then
  insert (Code, Descr, Active) 
    values (Code, Descr,  y );

结果:

Code Descr      Active
---- ---------- ------
1    Desc 1     y
2    Desc 2     n
2    Desc 2     y

友情链接