我需要帮助在表中执行插入使用下面的查询我插入记录在hConn
表
--Insert Into Conn table
INSERT INTO hConn
(hp10_id, hp09_id, hp_e_id,hp_s_id, hp_createddate, hp_updatedate,hp_insertby,hp_updateby)
SELECT
p10_id, p09_id, e_id,s_id, Getdate() AS hp_createddate, Getdate() AS hp_updatedate,
'1' AS hp_insertby,'1' AS hp_updateby
FROM p10_table
INNER JOIN p09_table
ON p10_p09_id = p09_id
INNER JOIN s_table
ON s_p_id = p10_id
INNER JOIN e_table
ON p_s_id = p10_id
WHERE
e010_lan_code = 'EN'
AND e009_lan_code = 'EN'
EXCEPT
SELECT hp10_id, hp09_id, hp_e_id, hp_s_id, Getdate(), Getdate(), 1, 1
FROM hConn
GO
在这一步之后,我用下面的语句
从hConn中删除了2条记录IF EXISTS(SELECT *
FROM hConn
WHERE hp10_id = 15
AND hp_e_id = 48
AND hp_s_id = 4)
BEGIN
DELETE FROM hConn
WHERE hp10_id = 15
AND hp_e_id = 48
AND hp_s_id = 4
END
IF EXISTS(SELECT *
FROM hConn
WHERE hp10_id = 11
AND hp_e_id = 48
AND hp_s_id = 4)
BEGIN
DELETE FROM hConn
WHERE hp10_id = 11
AND hp_e_id = 48
AND hp_s_id = 4
END
GO
下次执行插入脚本时,我不希望这2条记录被插入hConn
表中,只希望这些记录在插入脚本的select
查询中是新的,但在执行语句时,2条删除的记录也被添加,我有点不想再将它们添加到hConn
表中。除了每次删除记录外,还有其他方法可以解决这个问题吗?
将删除的记录存储在另一个表中,并在插入hConn
表之前检查是否正确?
更简单的方法是使用游标获取一行,然后检查删除条件,如果条件满足,则创建delete sql