Inserting Guid into SQL Server

How to insert a NEWID() / GUID / UUID into the code editor?

NEWID() itself is a function. when called returns a GUID value.

You do not have to put it in a separate window and then copy paste value from there. Just simply put that function there where you want the GUID value and when the query is executed at run time the value returned by this function will be used.

For instance in an Insert statement

INSERT INTO TableName (Col1 , Col2, Col3)
VALUES (1 , 'Value 1', NEWID())  

If you want col3 to have a GUID value you do not need to copy paste the value returned from NEWID() function but you use the function itself. At runtime a guid value will be retuned and inserted into col3.

Similarly if you were updating

UPDATE TableName 
  SET Col3 = NEWID()
WHERE <Some Condition>

Again you dont have to copy paste the value returned from NEWID() function just use the function itself.

Another Option would be suppose you are somewhere inside your code where you cannot call the NEWID() function . You would Declare a variable of type UNIQUEIDENTIFIER call the function store its value to that variable and then use that variable inside you code something like ...

DECLARE @GUID_Value UNIQUEIDENTIFIER;
 SET @GUID_Value = NEWID();

-- Now use this variable anywhere in your code.  

Adding to Keyboard Shortcut

For some strange reason if you want to add a shortcut to your SSMS to generate GUIDs for you. You would need to two thing.

1. Create a stored Procedure which returns GUID value .
2. Add a key shortcut to call that stored Procedure.

Proc Definition

CREATE PROCEDURE get_Guid
AS 
 SELECT NEWID();

Add it to shortcuts

From your SSMS goto Tools --> Options --> Environment --> Keyboard

add the stored procedure's name to the shortcut you want to. Click OK. Close SSMS and reopen it again and you are good to go.

As shown in the above snipshot, now if you press CTRL + 0 it will generate a GUID value for you in the same query window.

Inserting GUID into SQL Server

Just cast it from a varchar.

DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_ReserveBook]
        @userID = CONVERT(uniqueidentifier, 'AE019609-99E0-4EF5-85BB-AD90DC302E70'),
        @bookID = 7,
        @dateReserved = N'09/03/2009',
        @status = 1

SELECT  'Return Value' = @return_value

How to generate a Guid in SQL Server?

This will generate a Guid in SQL Server:

SELECT NEWID()

Creating New GUID automatically while inserting a new row to an existing table Not Working

Sourav's question about triggers got me thinking, so I tried a little test. Why?

Imagine a scenario where an application has already been written with thousands of INSERT statements that leave off the column list. In this case, if you could write an INSTEAD OF INSERT trigger that provides the column list, you could hopefully save yourself from correcting thousands of INSERT statements due to a newly added column.

Off the top of my head, I admittedly did not know if this could work.

So I wrote this little test:

CREATE TABLE tt (ColA varchar(1));

INSERT INTO tt VALUES ('a');

ALTER TABLE tt
ADD ColB uniqueidentifier DEFAULT NEWID();

GO

CREATE TRIGGER tr_tt
ON tt
INSTEAD OF INSERT
AS 

INSERT INTO tt (ColA)
SELECT ColA FROM inserted;

GO

INSERT INTO tt VALUES ('a');

SELECT * FROM tt;

DROP TABLE tt;

I also tried a variation of the TRIGGER with the following INSERT just to be thorough:

INSERT INTO tt (ColA, ColB)
SELECT ColA, NEWID() FROM inserted;

The result was the same in both cases: The same error as reported in the question. So to answer the question:

Can't we use a trigger here which can do it?

The answer is NO. Even if you put an INSTEAD OF INSERT TRIGGER on the table, the parser will still not let you write an INSERT..VALUES() statement unless the number and order of VALUES exactly matches the definition of the table. A TRIGGER cannot be used to get around it.

Sooner or later, lazy coding exacts its price.

MS SQL table with GUID All Zeros

uniqueidentifier data type does not mean that it will be unique. If you generate NEWID() then it generates unique id but again there is always a probability that same id may be generated.

For 0's

insert into t values ('00000000-0000-0000-0000-000000000000');
insert into t values ('00000000-0000-0000-0000-000000000000');
insert into t values (newid());

statements are valid. If your uid column is not primary key or has unique index on it, dublicate keys can be added to the table.

---

If you add check constraint to your table you can restrict and also identify the root cause of the problem

create table t (
  id uniqueidentifier unique
  CONSTRAINT CHK_uid CHECK (id != '00000000-0000-0000-0000-000000000000')
);
GO

✓

insert into t values ('00000000-0000-0000-0000-000000000000');
insert into t values ('00000000-0000-0000-0000-000000000000');
insert into t values ('00000000-0000-0000-0000-000000000000');
insert into t values (newid());
GO

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_uid". The conflict occurred in database "fiddle_80c5a5fe96ab4e73ac5dafbb2256025d", table "dbo.t", column 'id'.
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the CHECK constraint "CHK_uid". The conflict occurred in database "fiddle_80c5a5fe96ab4e73ac5dafbb2256025d", table "dbo.t", column 'id'.
Msg 547 Level 16 State 0 Line 3
The INSERT statement conflicted with the CHECK constraint "CHK_uid". The conflict occurred in database "fiddle_80c5a5fe96ab4e73ac5dafbb2256025d", table "dbo.t", column 'id'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.
Msg 3621 Level 0 State 0 Line 3
The statement has been terminated.

select * from t
GO

| id                                   |
| :----------------------------------- |
| ddeb79f6-dc0f-4c6a-a065-2083d39a78c1 |

db<>fiddle here

Insert GUID into sql table uniqueidentifier field - Groovy

UUID uuid = UUID.randomUUID()
//long lTimeStamp = d.getTime();
String tableInsert = "INSERT INTO tblTest (guidField,strField,dateField)" +
                              " VALUES ('" + uuid + "'"
                                        ",'test'" +
                                        ", getdate()"
                                        ")";
sql.execute(tableInsert);

Do you need your guid into your application? If not, let SQL server generate one for you :

String tableInsert = "INSERT INTO tblTest (guidField,strField,dateField)" +
                              " VALUES (newid()"
                                        ",'test'" +
                                        ", getdate()"
                                        ")";
sql.execute(tableInsert);

---

Related Topics