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
.
精彩评论