INSERT(part_number,part_description.What you essentially want to do is to make the combination of user and location unique across you table. OUTPUT DELETED.part_id, B.* INTO part_duplicates If you want to store repeated data into a second table include the OUTPUT Clause at the end of the UPDATE statement (which works as a DO NOTHING actually). WHEN NOT MATCHED THEN INSERT(part_number,part_description.) WHEN MATCHED THEN UPDATE SET A.part_num=A.part_num YOUR SELECT QUERY HERE NO NEED TO USE DISTINCT / WHERE Use the following code for skip inserting conflict cases: MERGE INTO parts A Using MERGE, you can decide what to do everytime you hit a conflict: this is either to update the previous inserted record or simply skip the new candidate for insertion SQL Server provides a command called: MERGE Suppose part_num 000345 does not exists in parts table the query will try to insert the 3 records (which are distinct each other).įor accomplish what you want. Suppose you have the following data on part_temp: part_num part_desc info man_id sub_id Neither commands will prevent you from dupplicating data: You are going to the wrong direction using DISTINCT and WHERE NOT EXISTS. Moot point, but I like to explicitly list the columns on an insert so that nothing breaks if the order of columns on the target table is different to that of the source query.įirst of all: there is no CONFLICT command in SQL SERVER. I guess what I am trying to say is that there isn't necessarily a quick win for this kind of problem and you need to do some good ol' fashion detective work using the clues provided by SQL. Luckily in your example it is a simple query and you can easily strip out joins to see if any of those introduce the duplicate, or if indeed the duplicate exists due to non-key attributes being different therefore causing your DISTINCT to not do what you expect it to do. Ultimately you need to strip this problem back to basics, as you have the duplicate value in question you need to start digging into the data and pay close attention to the source query.
0 Comments
Leave a Reply. |