从具有复合主键的另一个表插入或更新表 [英] INSERT or UPDATE table from another table with composite primary key

问题描述

我正在寻找直接从 SQL 执行以下操作的正确语法和方法:从 TableA 中包含的数据插入或更新(如果数据已存在于其中)TableMain两者都具有相同的复合主键.

I am looking for the correct syntax and way to do the following directly from SQL: insert or update (if data already exists inside) TableMain from data contained in TableA with both having same composite primary key.

两个表都定义为:

CREATE TABLE TableA (
[TID0] [int] NOT NULL,
[TID1] [int] NOT NULL,
[language] [nvarchar](2) NOT NULL,
[TID2] [nvarchar](200) NOT NULL,
[text] [nvarchar](max) NULL,
[updatedOn] [datetime] NOT NULL  DEFAULT (getdate())
PRIMARY KEY (
    [TID0],
    [TID1],
    [language],
    [TID2],
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

TableA 会定期删除和填充.

TableMain 与定义相同，但将包含更多行数据，我需要的是将 TableA 中从未见过的值插入 TableMain>，并更新现有的行.

TableMain as the same definition but will contain many more rows of data and what I need is to insert never seen values from TableA into TableMain, and update already existing rows.

我曾经做过这种插入，但我不知道如何处理更新和复合主键:

I used to do this kind of insert but I do not know how to handle update and composite primary keys :

INSERT INTO TableMain 
    SELECT * FROM TableA

我正在使用 SQL Server 9.00.5000

受 MERGE 和 启发的另一种方式模仿它

DECLARE @updatedIDs TABLE(
    [TID0] [int],
    [TID1] [int],
    [language] [nvarchar](2),
    [TID2] [nvarchar](200),
                PRIMARY KEY ([TID0], [TID1], [language], [TID2])  -- as stated by Nikola Markovinović above, thanks
);

-- First update records 
update TableMain 
   set [text]      = source.[text], 
       [updatedOn] = source.[updatedOn] 
       OUTPUT
         inserted.[TID0]
         inserted.[TID1]
         inserted.[language]
         inserted.[TID2]
       INTO @updatedIDs
  from 
       TableMain AS main
       , TableA AS source 
  WHERE    
   TableMain.[TID0]         = source.[TID0] 
   and TableMain.[TID1]     = source.[TID1] 
   and TableMain.[language] = source.[language] 
   and TableMain.[TID2]     = source.[TID2] 

-- And then insert 

insert into TableMain
select *
  from TableA AS source 
 where not exists 
       ( 
      select 1
        from @updatedIDs AS i
       where i.[TID0]     = source.[TID0] 
         and i.[TID1]     = source.[TID1] 
         and i.[language] = source.[language] 
         and i.[TID2]     = source.[TID2] 
       )

推荐答案

以下是您可以用来更新数据的脚本:

Here is a script you might use to upsert your data:

-- On error transaction is automatically rolled back
set xact_abort on

begin transaction
-- First update records
update TableMain
   set [text]      = source.[text],
       [updatedOn] = source.[updatedOn]
  from TableMain
 inner join TableA source
    on TableMain.[TID0]     = source.[TID0]
   and TableMain.[TID1]     = source.[TID1]
   and TableMain.[language] = source.[language]
   and TableMain.[TID2]     = source.[TID2]

-- And then insert

insert into TableMain ([TID0], [TID1], [language], [TID2], [text], [updatedOn])
select [TID0], [TID1], [language], [TID2], [text], [updatedOn]
  from TableA source
 where not exists
       (
          select *
            from TableMain
           where TableMain.[TID0]     = source.[TID0]
             and TableMain.[TID1]     = source.[TID1]
             and TableMain.[language] = source.[language]
             and TableMain.[TID2]     = source.[TID2]
       )

commit transaction

如果性能不理想，您可以将 not exists() 重写为 left join ... where TableMain.TID0 is null.

You might rewrite not exists() as left join ... where TableMain.TID0 is null if performance is not satisfactory.

这篇关于从具有复合主键的另一个表插入或更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！