用复合键插入或更新另一个表
我想直接找到正确的语法和方法SQL如下：插入或更新（如果数据已经内部存在）TableMain从数据中包含TableA两者都有相同的复合键。这两个表的定义如下：
CREATE TABLE TableA ( NOT NULL, NOT NULL, (2) NOT NULL, (200) NOT NULL, (max) NULL, NOT NULLDEFAULT (getdate())PRIMARY KEY, , , ,)) ON TEXTIMAGE_ON TableA 定期删除并填充。
TableMain因为定义相同，我需要的是更多的行包含数据，从未见过插入值TableA入TableMain，并更新现有行。
我做过这个插入，但我不知道如何处理更新和复合键：
INSERT INTO TableMain SELECT * FROM TableA编辑：我正在使用 SQL Server 9.00.5000
编辑：另一种受MERGE启发和模仿它的方法
TableA AS source WHERE TableMain. = source. and TableMain. and TableMain. = source. and TableMain.-- And then insertinsert into TableMainselect *from TableA AS sourcewhere not exists ( select from @updatedIDs AS i where i. and i. = source. and i. = source. and TableMain. = source. and TableMain. = source. and TableMain. = source.-- And then insertinsert into TableMainselect *from TableA AS sourcewhere not exists ( select 1 from @updatedIDs AS i where i. = source. and i. = source. and i. = source. and i. = source. )
解决方案:
这个脚本可以用来添加数据：
-- On error transaction is automatically rolled backset xact_abort onbegin transaction-- First update recordsupdate TableMain set = source., = source.from TableMain inner join TableA source on TableMain. and TableMain. and TableMain. = source. and TableMain.-- And then insertinsert into TableMain (,,,,,)select ,,,,,from TableA source where not exists select * from TableMain where TableMain. = source. and TableMain.[TID = source.[TID )commit transaction你可以重写not exist（），left join ... where TableMain.TID0 is null性能似乎并不令人满意。
页:
[1]