sqlserver2000,存储过程如下
CREATE PROCEDURE INSERTBKTMPTOBK AS
BEGIN
UPDATE DICT_BOOKCARD SET DICT_BOOKCARD.BOOKNAME= DICT_BOOKCARD_TMP.BOOKNAME,
DICT_BOOKCARD.AUTHORS= DICT_BOOKCARD_TMP.AUTHORS,
DICT_BOOKCARD.ISBN= DICT_BOOKCARD_TMP.ISBN,
DICT_BOOKCARD.ISBNCODE= DICT_BOOKCARD_TMP.ISBNCODE,
DICT_BOOKCARD.PUBCODE= DICT_BOOKCARD_TMP.PUBCODE,
DICT_BOOKCARD.PUBLISH= DICT_BOOKCARD_TMP.PUBLISH,
DICT_BOOKCARD.PRICE= DICT_BOOKCARD_TMP.PRICE,
DICT_BOOKCARD.ISSUE=DICT_BOOKCARD_TMP.ISSUE,
DICT_BOOKCARD.UCS_CODE=DICT_BOOKCARD_TMP.UCS_CODE
FROM DICT_BOOKCARD ,DICT_BOOKCARD_TMP WHERE DICT_BOOKCARD.BCD_ID=DICT_BOOKCARD_TMP.BCD_ID
DELETE FROM DICT_BOOKCARD_TMP WHERE BCD_ID IN (SELECT BCD_ID FROM DICT_BOOKCARD)
INSERT INTO DICT_BOOKCARD(BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS)
SELECT BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS FROM DICT_BOOKCARD_TMP
GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS
TRUNCATE TABLE DICT_BOOKCARD_TMP
END
GO
目的就是每次将dict_bookcard_tmp零时表记录插入dict_bookcard正式表,存在则更新,不存在就插入,正式表bcd_id有唯一索引,零时表有不唯一索引,目前执行很慢,求优化,每次条目有可能几百条,也有可能上10万这样.
------解决方案--------------------
語句本身不是很複雜，不過可以使用別名將你的代碼簡化下。
CREATE PROCEDURE INSERTBKTMPTOBK AS
BEGIN
UPDATE
A
SET
A.BOOKNAME= B.BOOKNAME,
A.AUTHORS= B.AUTHORS,
A.ISBN= B.ISBN,
A.ISBNCODE= B.ISBNCODE,
A.PUBCODE= B.PUBCODE,
A.PUBLISH= B.PUBLISH,
A.PRICE= B.PRICE,
A.ISSUE=B.ISSUE,
A.UCS_CODE=B.UCS_CODE
FROM
DICT_BOOKCARD A ,DICT_BOOKCARD_TMP B
WHERE A.BCD_ID=B.BCD_ID
DELETE FROM DICT_BOOKCARD_TMP WHERE BCD_ID IN (SELECT BCD_ID FROM DICT_BOOKCARD)
INSERT INTO DICT_BOOKCARD(BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS)
SELECT BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS FROM DICT_BOOKCARD_TMP
GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS
TRUNCATE TABLE DICT_BOOKCARD_TMP
END
GO
------解决方案--------------------
先用 if exists 语句判断记录是否存在，
然后存在的话，做Update
不存在的话做Insert