update or insert value - stored procedure - 'upsert'

Reading more carefully it seems you are right. I won't post a fixed solution, as this doesn't really make any sense.

Please metropia advise on what should be the unique key...

Giannis

Hi, thank you for posting a solution and pointing out the non sense parts of my question.

I went back to the user to explain that making quantity column the unique key, was not clear. They review the data they were given me, and add the two rows together since there was no need to have two entries that looked exactly the same but the quantity.

I am running this as a SSIS package and added some data validation checks to look for these type of scenarios.

I will award points to the best of my abilities, and I thank you much for your posts to this question.

regards.

I am sorry, but i do not get what the question is here. Can you please clarify?

Giannis

i read from an excel file and insert rows into sql server table.

sometimes the user that creates the excel file, creates several rows for same customer, fiscal year, period type, the only thing that makes each row different is the quantity.

when i run the stored procedure, read the file, i would like to be able to check if the record exists or not. the only thing that can make a record different is the quantity. so, eventhough may not be the recommended way to do this, the entries:

ANDES      2013      067       67,300
ANDES      2013      067       6,000

are different, thus, two entries should be created (insert)

when i run my package, i was getting error, cannot insert duplicate row after inserting
ANDES      2013      067       67,300

and going to the next:
ANDES      2013      067       6,000

the error states cannot insert duplicate row based on values ('ANDES', '2013', '067') because those three columns make a clustered pk in the table (i think)

i hope this offers a bit more clarity :)

Thank you Giannis. I was thinking about adding the identity column as a matter of fact.
If I do this, then should I give your code a try?
Thank you.

Yes, it should work. You have to consider though why these columns were a part of the key in the first place, as you may break something when changing the structure of the table.

I will be careful before making any modifications.

Thank you.

James,  I went and talk to the user that is creating this file and explained to him the issues I ran into, when having these two entries that basically, and like you suggest, can be summed up into one row only. He agreed to fixing the data, and resend a new file.

Thank you for following up.

Regards.

James0628,

regarding this part of your last comment:

" if you already had a row in the table for ANDES/2013/067 and then there was another row in the data, you'd just add the quantity to the existing row."

That would be awesome, and smart to learn how to do.