• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

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!
  • 3
1 Solution
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.
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?)
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.
tenchulyoAuthor Commented:
No one provided a better solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now