Geek Answers Handbook

Sql server connection table values ​​are saved four times in one load

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

Questions Answers Test QAT --------- --------- ------ ----- Questid Ansid Testid Questid Question Answers Testname Ansid Testid id StdUsername

Launch 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 boot from the asp.net application, but on the second boot it saves the data 4 times in the QAT table instead of some, but the other tables are in order. I need urgent help.

+4
source share
1 answer

You may have duplicate questions and answers in your data. DISTINCT gets rid of duplicates that are currently present in the load table, but the next time you call your proc, they are inserted again because they do not take into account the values ​​already present in the table.

Use this to insert answers:

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

and a similar query for questions .

+1
source

More articles:


All Articles