Thank you all, I think the idea that clean up data first then insert is great. So that there will no primary key violation.

Let me do some test and get...

Thank you all.

I will give a try for the code

Great, that helps a lot!

Thanks!

I'm using windows 7, there is not that option.

Thanks

I tried you query again, it works this time.

Thank you!

Thanks.

This is actually not a table, just an excel file from a staff. I imported into the sql server to do the data manipulation.

I am able to do it by...

Thanks,

but I still get multiple records for each person.

I only want the first records

Thank you.

Error:18456. severity:14, State:16

Thank you very much, I will try that.

Thank you very much, will change to small date time to store the birthdate.

Thanks, in the application log, I found there is msinstaller that tried to apply windows update Sql server sp3, and book online, for some reason it failed.

I don't need sp3...

Thanks

Thanks a lot

It works great.

Thanks

By the way is this EXcept only works in SQL?

Is it working in Microsoft Access, don't think so.

If the same situation in Access, how can I do it?

Thanks

Any help Please??