|
我想直接找到正确的语法和方法SQL如下:插入或更新(如果数据已经内部存在)TableMain从数据中包含TableA两者都有相同的复合键。
1 h1 y7 j: B7 z6 V2 d3 o这两个表的定义如下:
5 T% Y% e" w/ R: J1 ^! ~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 定期删除并填充。3 ^& s! ~: {4 G3 S" \8 v k+ e
TableMain因为定义相同,我需要的是更多的行包含数据,从未见过插入值TableA入TableMain,并更新现有行。
# @( {: c& }, K$ N我做过这个插入,但我不知道如何处理更新和复合键:# R* c7 I! [& ~
INSERT INTO TableMain SELECT * FROM TableA编辑:我正在使用 SQL Server 9.00.5000: R6 B9 Y3 W6 O* i: E! y
编辑:另一种受MERGE启发和模仿它的方法) M4 C3 K2 Y7 Y6 p |8 u
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] )
* j) y1 W; P7 W! X$ J& V 解决方案: & }7 z( |) X& F, I+ r* I# i
这个脚本可以用来添加数据:0 U! t% L6 D6 j$ c
-- 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性能似乎并不令人满意。 |
|