语句:INSERT INTO FPTask(appid,prodid,NextWork,Latest,BdData,TkIssues,Leadership,state,AddYM)
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp left join FPTask AS fpt ON 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06'
怎么样在插数据的同时,检查下该是不是已存在
如果不存在的话,再向该插入数据!!!
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp left join FPTask AS fpt ON 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06'
怎么样在插数据的同时,检查下该是不是已存在
如果不存在的话,再向该插入数据!!!
AND fpt.AddYM='2008-06')
INSERT INTO FPTask
(appid,prodid,NextWork,Latest,BdData,TkIssues,Leadership,state,AddYM)
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp left join FPTask AS fpt ON 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06'
INSERT INTO FPTask(appid,prodid,NextWork,Latest,BdData,TkIssues,Leadership,state,AddYM)
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp left join FPTask AS fpt ON 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06'
2、用触发器比较inserted表与要插入表
(appid,prodid,NextWork,Latest,BdData,TkIssues,Leadership,state,AddYM)
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp
where not exists( select 1
from FPTask AS fpt
where 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06')
insert into ....
SELECT * FROM (
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp left join FPTask AS fpt ON 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06') A
WHERE NOT EXISTS(SELECT 1 FROM FPTask WHERE appid=A.appid AND prodid=A.prodid)
INSERT INTO FPTask(appid,prodid,NextWork,Latest,BdData,TkIssues,Leadership,state,AddYM)
SELECT 16 AS AppId, fp.id AS ProdId, fpt.NextWork, fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership, 0 AS State, '2008-07' AS AddYM
FROM PlatFormProd AS fp left join FPTask AS fpt ON 16 = fpt.appid AND fp.id = fpt.prodid
AND fpt.AddYM='2008-06'
where
checksum(16,fp.id,fpt.NextWork,fpt.Latest,fpt.BdData,fpt.TkIssues,fpt.Leadership,0,'2008-07') not in
(select checksum(appid,prodid,NextWork,Latest,BdData,TkIssues,Leadership,state,AddYM) from FPTask)
insert into ct select 9,'abc'
create trigger DU_Check
on Ct
for insert,update
as
begin
if exists(select 1 from ct,inserted where ct.cName=inserted.cName)
begin
raiserror ('记录重复',16,1)
rollback
end
end服务器: 消息 50000,级别 16,状态 1,过程 DU_Check,行 8
记录重复
on Ct
instead of insert,update
as
begin
if exists(select 1 from ct,inserted where ct.cName=inserted.cName)
begin
raiserror ('记录重复',16,1)
rollback
end
else
begin
insert into ct select * from inserted
end
end