|
我想直接找到正确的语法和方法SQL如下:插入或更新(如果数据已经内部存在)TableMain从数据中包含TableA两者都有相同的复合键。
7 u! Q7 L4 u( v. g' ~ e这两个表的定义如下:5 }( y. E, q u2 g5 ]5 i
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 定期删除并填充。9 J/ J/ x0 z$ g, U2 u5 \
TableMain因为定义相同,我需要的是更多的行包含数据,从未见过插入值TableA入TableMain,并更新现有行。( @ I- B" Y8 X( n; }& V
我做过这个插入,但我不知道如何处理更新和复合键:
. S) [2 W/ @- |% R- G1 oINSERT INTO TableMain SELECT * FROM TableA编辑:我正在使用 SQL Server 9.00.5000- a2 V) u- F% h) q
编辑:另一种受MERGE启发和模仿它的方法/ E7 m+ Z3 @3 L7 X: g5 o$ [0 r
TableA AS source WHERE TableMain.[TID0] = source.[TID0] and TableMain.[TID = source.[TID1] and TableMain.[language] = source.[language] and TableMain.[TID = source.[TID2]-- And then insertinsert into TableMainselect * from TableA AS source where not exists ( select from @updatedIDs AS i where i.[TID = source.[TID0] and i.[TID = source.[TID and i.[language] = source.[language] and i.[TID = source.[TID ) 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 insertinsert into TableMainselect * 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] )
3 ^. S9 K( ]/ W& ~8 x 解决方案: % V/ @2 f9 {& l' a
这个脚本可以用来添加数据:3 U3 h4 ^/ g3 Y8 G
-- On error transaction is automatically rolled backset xact_abort onbegin transaction-- First update recordsupdate TableMain set [text] = source.[text], [updatedOn] = source.[updatedOn] from TableMain inner join TableA source on TableMain.[TID = source.[TID0] and TableMain.[TID = source.[TID1] and TableMain.[language] = source.[language] and TableMain.[TID = source.[TID2]-- And then insertinsert 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.[TID = source.[TID and TableMain.[TID = source.[TID and TableMain.[language] = source.[language] and TableMain.[TID = source.[TID )commit transaction你可以重写not exist(),left join ... where TableMain.TID0 is null性能似乎并不令人满意。 |
|