开发者

sql server Junction table values saves four times in one upload

I uploaded a csv file to a dummy table(csv_upload) in sql server 2008. I have to distribute the data to the tables below using a trigger;

Questions          Answers       Test              QAT
---------         ---------     ------            -----
Questid           Ansid          Testid           Questid
Question          Answers        Testname         Ansid
                                                  Testid
               开发者_运维百科                                   id
                                                  StdUsername

The trigger code;

 INSERT INTO tbl_answers ( Answer)
select  DISTINCT Answer  
from tbl_csv_upload


INSERT INTO tbl_questions ( Question )
select DISTINCT Question 
from tbl_csv_upload 

INSERT INTO tbl_taqa (StdUsername,questid, ansid , testid ) 
 SELECT  StdUsername ,q.quest_id,a.ans_id,t.test_id
  FROM csv_upload c, questions q, answers a, test t
WHERE c.Question = q.Question
AND c.Answer = a.Answer 
AND t.test_id = IDENT_CURRENT('test')

This trigger worked well for the first upload from an asp.net application but on second upload it saves the data 4 time in the QAT table instead of ones but other tables are ok. Pls i need urgent help.


Probably, you have duplicates of questions and answers in your data. DISTINCT gets rid of duplicates currently present in the upload table, but the next time your are calling your proc they get inserted again since it does not take into account values already present in the table.

Use this to insert the answers:

INSERT
INTO    answers (answer)
SELECT  answer
FROM    tbl_csv_upload
EXCEPT
SELECT  answer
FROM    answers

and a similar query for questions.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜