0

I am new to SQL Server. Working with version 2014.

I have a table with GUID as the key. I have an insert statement

IF NOT EXISTS (select guid from table)
 insert into table (,,) values (,,);

In this case, the insert will work if the guid does not exist; if it exists, I need to generate a new guid and perform the insertion. This does not stop with just two levels. I need to check for every guid that is generated and then insert accordingly. How do I do this with a query or, in the worst case, a stored procedure?

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
gaenshr
  • 19
  • 4

1 Answers1

2

Alter the table make the GUID column auto generated. Then you don't have check for its existence every time during insertion.

CREATE TABLE Your_Table
(
Guid_ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
Column_1 ...
)

If you cannot alter the table then

;with cte as
(
select newid(),value1,value2,..
)
Insert into Your_Table(Guid_ID,col1,col2,..)
Select newid(),value1,value2,..
from cte C
Where not exists (select 1 from Yourtable b where b.Guid_ID = c.Guid_ID ) 
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Any particular reason for choosing `NEWSEQUENTIALID()` and not `NEWID()`? – Gordon Linoff Feb 18 '16 at 01:56
  • @GordonLinoff [Here](http://stackoverflow.com/questions/1587185/newid-vs-newsequentialid-what-are-the-differences-pros-and-cons) is the reason – Pரதீப் Feb 18 '16 at 01:59
  • Thanks for the solution. It works. Unfortunately, using CTE is not an acceptable solution across the organization. I am trying to use while (if not exist select 1 from table where guid=@guid) begin insert..... End. Doesn't seem to work. Any other solutions? – gaenshr Feb 18 '16 at 09:48