SQL issue Newid() not working properly

I have the following SQL Command:

INSERT INTO  mstr_lists (mstr_list_item_id,mstr_list_type,mstr_list_item_desc,list_order,cache_ind,delete_ind,create_timestamp,created_by,modify_timestamp,modified_by, show_in_emr_ind,enterprise_id,practice_id,nxmd_ind)
select distinct NEWID(),'location',txt_provider_lname ,0,'N','N',GETDATE(),'817',GETDATE(),'817','Y','00000','0000','N' from patient_provider_role_ where txt_provider_lname is not null and txt_provider_lname != ''
and txt_provider_lname not in
(select mstr_list_item_desc from mstr_lists where mstr_list_type = 'location')

I get error message:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.mstr_lists' with unique index 'ak_mstr_lists1'.
The statement has been terminated.


I double checked and there were no duplicates in the query as I have used distinct in my select?

Can someone help me figure this out?

Thanks in advance!
tenchulyoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Highlight just the SELECT clause, execute, and eyeball whether the NEWID is generated once for all rows, or if it is a different value for each row.

It's also worth eyeballing constraint 'ak_mstr_lists1' to see if it is on a different column then the one NEWID() is populating.

btw I wrote an article on How to populate a million sample people where I'm using NEWID() in a cursor, so if this can be pulled off in a single UPDATE I'd be very interested.
0
tenchulyoAuthor Commented:
Yes... I did this before elsewhere so I know the syntax should be right...


Different value for each row... I had already tried that... I'm thinking this might be a Microsoft oops because I can't understand why this wouldn't work... I hardly ever ask questions thanks to good ol Google but this one is driving me nuts!

The constraint actually refers to txt_provider_lname, I ordered by that field and found dups (probably because of the newid(), but just for certain random fields?)
0
tenchulyoAuthor Commented:
OK...I think I got it on my own...

The command works perfectly if you have no duplication on the original field, the distinct doesn't work because you are taking the newid() as a consideration. The only solution I was able to come up with was a #temp table to filter out the junk then the insert on top of that.

This worked as I needed... If anyone can come up with how to do it in one transaction than that would be awesome... but for now...this is my solution to myself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tenchulyoAuthor Commented:
No one provided a better solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.