Início / dba / Perguntas / 212003
Accepted
user2676140
Asked: 2018-07-13 03:25:37 +0800 CST

Ajuda com a declaração de mesclagem

  • 772

Estou querendo comparar o Distinct snfrom HMI_Tempto snna minha tabela HMI-> se snexistir, quero atualizar os valores - consegui essa sintaxe CHECK! - se snNÃO existir , quero inserir os dados de HMI_TempTentei HMIesta sintaxe, mas recebo erros de:

Nome de coluna inválido 'cb'
Nome de coluna inválido 'lp' Nome de
coluna inválido cn Nome de
coluna inválido stn

E esta é a minha sintaxe - como devo reescrever isso para obter o resultado desejado?

MERGE INTO HMI AS Target
USING (SELECT DISTINCT sn FROM HMI_Temp) AS Source ON Target.sn = Source.sn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

DDL:

CREATE TABLE [dbo].[HMI_Temp]
(
    [sID] [int] IDENTITY(1,1) NOT NULL,
    stn [float] NULL,
    [sn] [nvarchar](255) NULL,
    [cn] [nvarchar](max) NULL,
    [lp] [nvarchar](max) NULL,
    [cb] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [sID] ASC
    ) WITH 
      (
           PAD_INDEX = OFF
         , STATISTICS_NORECOMPUTE = OFF
         , IGNORE_DUP_KEY = OFF
         , ALLOW_ROW_LOCKS = ON
         , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
) ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY]  
GO

SET IDENTITY_INSERT [dbo].[HMI_Temp] ON 
GO

INSERT [dbo].[HMI_Temp] ([sID], stn, [sn], [cn], [lp], [cb]) 
VALUES (1, 8888, N'Test', N'Test', N'Test123', N'Test456')
VALUES (1, 8888, N'SecondTest', N'SecondTest', N'SecondTest123', N'SecondTest456')
GO

Esta é a instrução de atualização que eu estava usando que funcionou, mas agora jogando uma chave nela para a inserção, se não existir :(

UPDATE y
SET y.lp = x.lp
,y.cb = x.cb
,y.cn = x.cn
FROM HMI y
INNER JOIN HMI_Temp x
ON y.stn = x.stn
AND y.sn = x.sn
AND y.cn = x.cn
sql-server sql-server-2008-r2

2 respostas

  1. Aqui está pelo menos uma parte do problema: se você tiver duas linhas HMI_Tempcom o mesmo valor, snmas valores diferentes para cb(ou qualquer outra coluna), qual delas o SQL Server deve escolher?

    Aqui está um padrão melhor do que MERGE, IMHO, que tem todos os tipos de problemas :

    INSERT dbo.HMI(stn,sn,cn,lp,cb)
SELECT stn,sn,cn,lp,cb
FROM
(
  SELECT stn,sn,cn,lp,cb,rn = ROW_NUMBER() OVER 
    (
      PARTITION BY sn,cn -- makes sn + cn distinct
      ORDER BY sID       -- picks which row to keep
    )
  FROM dbo.HMI_Temp
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.HMI WHERE sn = hmi.sn
  )
) AS x
WHERE rn = 1;

    Isso é pegar a linha com a menor sID, mas você pode querer classificar as linhas de uma maneira diferente ou usar MIN/ MAXem cada uma das outras quatro colunas para determinar qual linha você deseja manter. Difícil dizer pela sua pergunta.

    • 5
  2. Best Answer

    Para evitar o erro de sintaxe, você precisa incluir todas as colunas da USINGcláusula que estão sendo inseridas na tabela de destino. Portanto, uma instrução de mesclagem sintaticamente válida seria:

    MERGE INTO HMI AS Target
USING (SELECT DISTINCT sn, lp, cb, cn, stn FROM HMI_Temp) AS Source ON Target.sn = Source.sn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

    Como a funcionalidade pretendida inclui a capacidade de atualizar linhas existentes, você também precisa de uma WHEN MATCHEDcláusula que defina as colunas desejadas na tabela de destino para esses valores da tabela de origem. Algo como:

    MERGE INTO HMI AS Target
USING (
    SELECT DISTINCT sn
        , lp
        , cb
        , cn
        , stn 
    FROM HMI_Temp
    ) AS Source ON Target.sn = Source.sn
WHEN MATCHED THEN
UPDATE SET Target.lp = Source.lp
    , Target.cb = Source.cb
    , Target.cn = Source.cn
    , Target.stn = Source.stn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) 
VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

    No entanto, como mencionado por Aaron em sua resposta, MERGEestá repleto de outros problemas em potencial.

    Eu usei o seguinte exemplo minimamente completo e verificável :

    USE tempdb;

DROP TABLE IF EXISTS dbo.HMI;
CREATE TABLE dbo.HMI
(
    sID int IDENTITY(1,1) NOT NULL
    , stn float NULL
    , sn nvarchar(255) NULL
    , cn nvarchar(max) NULL
    , lp nvarchar(max) NULL
    , cb nvarchar(max) NULL
    , PRIMARY KEY CLUSTERED 
      (
          sID ASC
      ) WITH 
        (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
) ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY];
GO

INSERT dbo.HMI (stn, sn, cn, lp, cb) 
VALUES (8888, N'Test', N'Test', N'Test123', N'Test456')
    , (8887, N'A', N'B', N'C', N'D');


DROP TABLE IF EXISTS dbo.HMI_Temp;
CREATE TABLE dbo.HMI_Temp
(
    sID int IDENTITY(1,1) NOT NULL
    , stn float NULL
    , sn nvarchar(255) NULL
    , cn nvarchar(max) NULL
    , lp nvarchar(max) NULL
    , cb nvarchar(max) NULL
    , PRIMARY KEY CLUSTERED 
      (
          sID ASC
      ) WITH 
        (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
) ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY];
GO

INSERT dbo.HMI_Temp (stn, sn, cn, lp, cb) 
VALUES (8888, N'Test', N'F', N'G', N'H')

    Os dados se parecem com:

    SELECT *
FROM dbo.HMI;

    ╔═════╦══════╦══════╦══════╦═════════╦═══╗═════╦═══╗═════╦═══╗
║ sID ║ stn ║ sn ║ cn ║ lp ║ cb ║
╠═════╬══════╬══════╬══════╬═════════╬═══╣════╬═══╣
║ 1 ║ 8888 ║ Teste ║ Teste ║ Teste123 ║ Teste456 ║
║ 2 ║ 8887 ║ A ║ B ║ C ║ D ║
╚═════╩══════╩══════╩══════╩═════════╩════════╩════════╩═══╝
    SELECT *
FROM dbo.HMI_Temp;

    ╔═════╦══════╦══════╦════╦════╦════╗
║ sID ║ stn ║ sn ║ cn ║ lp ║ cb ║
╠═════╬══════╬══════╬════╬════╬════╣
║ 1 ║ 8888 ║ Teste ║ F ║ G ║ H ║
╚═════╩══════╩══════╩════╩════╩════╝

    A instrução de mesclagem:

    MERGE INTO dbo.HMI AS Target
USING (
    SELECT DISTINCT sn
        , lp
        , cb
        , cn
        , stn 
    FROM dbo.HMI_Temp
    ) AS Source ON Target.sn = Source.sn
WHEN MATCHED THEN
    UPDATE SET Target.lp = Source.lp
        , Target.cb = Source.cb
        , Target.cn = Source.cn
        , Target.stn = Source.stn
WHEN NOT MATCHED THEN
    INSERT (lp, cb, cn, sn, stn) 
    VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

    Os resultados, poste MERGE:

    SELECT *
FROM dbo.HMI;

    ╔═════╦══════╦══════╦════╦════╦════╗
║ sID ║ stn ║ sn ║ cn ║ lp ║ cb ║
╠═════╬══════╬══════╬════╬════╬════╣
║ 1 ║ 8888 ║ Teste ║ F ║ G ║ H ║
║ 2 ║ 8887 ║ A ║ B ║ C ║ D ║
╚═════╩══════╩══════╩════╩════╩════╝

    Se a dbo.HMI_Temptabela contiver linhas não exclusivas, como em:

    INSERT dbo.HMI_Temp (stn, sn, cn, lp, cb) 
VALUES (8888, N'Test', N'F', N'G', N'H')
    , (8888, N'Test', N'I', N'J', N'K')
    , (8888, N'Test', N'L', N'M', N'N');

    Ao executar a MERGEinstrução, você verá este erro:

    Msg 8672, Level 16, State 1, Line 67
    A instrução MERGE tentou UPDATE ou DELETE a mesma linha mais de uma vez. Isso acontece quando uma linha de destino corresponde a mais de uma linha de origem. Uma instrução MERGE não pode atualizar/excluir a mesma linha da tabela de destino várias vezes. Refine a cláusula ON para garantir que uma linha de destino corresponda no máximo a uma linha de origem ou use a cláusula GROUP BY para agrupar as linhas de origem.

    Como agora você esclareceu que também precisa usar a cncoluna como unificador, talvez seja possível usar esta instrução de mesclagem:

    MERGE INTO dbo.HMI AS Target
USING (
    SELECT sn
        , lp
        , cb
        , cn
        , stn 
    FROM dbo.HMI_Temp
    ) AS Source ON Target.sn = Source.sn
        AND Target.cn = Source.cn
WHEN MATCHED THEN
    UPDATE SET Target.lp = Source.lp
        , Target.cb = Source.cb
        , Target.cn = Source.cn
        , Target.stn = Source.stn
WHEN NOT MATCHED THEN
    INSERT (lp, cb, cn, sn, stn) 
    VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

    Observe, eu adicionei AND Target.cn = Source.cnà WHEREcláusula.

    • 4

relate perguntas