語句本身不是很複雜,不過可以使用別名將你的代碼簡化下。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_TMPEND GO
先用 if exists 语句判断记录是否存在, 然后存在的话,做Update 不存在的话做Insert
刪除和插入兩步可以合成一步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 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 WHERE BCD_ID IN (SELECT BCD_ID FROM DICT_BOOKCARD) GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS TRUNCATE TABLE DICT_BOOKCARD_TMPEND
CREATE PROCEDURE INSERTBKTMPTOBK AS BEGINUPDATE 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_IDINSERT INTO DICT_BOOKCARD SELECT BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS FROM DICT_BOOKCARD_TMP A, BCD_ID B Where A.BCD_ID <> B.BCD_ID GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORSTRUNCATE TABLE DICT_BOOKCARD_TMPEND GO
INSERT INTO DICT_BOOKCARD SELECT BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS FROM DICT_BOOKCARD_TMP A, BCD_ID B Where A.BCD_ID <> B.BCD_ID GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS------------ 這種寫法是很有問題的,不能這麼用。
------------ firefox的寫法是不正確的,千萬不能這麼寫,不然你的數據庫中會有很多重復的紀錄。Create Table A(BCD_ID Int) Create Table B(BCD_ID Int) Insert A Select 1 Union All Select 2Insert B Select 2 Union All Select 3Select B.* From A, B Where A.BCD_ID <> B.BCD_IDDrop Table A, B --Result /* BCD_ID 2 3 3 */看出問題沒有,2這條紀錄已經在A中了,但是這麼查詢還是會將2這條紀錄查詢出來。
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_TMPEND
GO
然后存在的话,做Update
不存在的话做Insert
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 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
WHERE BCD_ID IN (SELECT BCD_ID FROM DICT_BOOKCARD)
GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS TRUNCATE TABLE DICT_BOOKCARD_TMPEND
BEGINUPDATE 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_IDINSERT INTO DICT_BOOKCARD
SELECT BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS FROM DICT_BOOKCARD_TMP A, BCD_ID B
Where A.BCD_ID <> B.BCD_ID
GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORSTRUNCATE TABLE DICT_BOOKCARD_TMPEND
GO
SELECT BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS FROM DICT_BOOKCARD_TMP A, BCD_ID B
Where A.BCD_ID <> B.BCD_ID
GROUP BY BCD_ID,BOOKNAME,ISBN,ISBNCODE,PUBLISH,PUBCODE,PRICE,AUTHORS------------
這種寫法是很有問題的,不能這麼用。
我改成not in 应该可以吧
----------
哦,不好意思,的確是要改為NOT IN.
鱼那个不对吧,insert 应该是插入bcd_id不在里面的记录吧????firefox这个应该正确
------------
firefox的寫法是不正確的,千萬不能這麼寫,不然你的數據庫中會有很多重復的紀錄。Create Table A(BCD_ID Int)
Create Table B(BCD_ID Int)
Insert A Select 1
Union All Select 2Insert B Select 2
Union All Select 3Select B.* From A, B Where A.BCD_ID <> B.BCD_IDDrop Table A, B
--Result
/*
BCD_ID
2
3
3
*/看出問題沒有,2這條紀錄已經在A中了,但是這麼查詢還是會將2這條紀錄查詢出來。