0

In some cases i work directly with data through the Edit top 200 (or what it is set to) in SSMS. I find it particularly useful when I get some sort of data that is pre-worked through Excel or similar from our customers and the volume is low.

But in come cases we have columns that are of type UniqueIdentifier (GUIDs) that are required which I else would use NewID() to generate when writing SQL directly.

So I have all information I need to just copy/paste data from lets say a sheet of excel into the GUI in SSMS "Edit top 200.." except from a column at will define a new GUID. I can't just leave it empty/NULL either, that will generate an error message.

The embedded example tries to show what I mean. The first row is already present with a GUID (#2) and have data for the rest also (#1). So I have data for every column that is needed for those under #1 with red square, but if I put in such data in an empty row I will need to create a GUID in the first empty column under #2.

Have anyone seen a way to achieve what I try to describe above? Is there a way to get the GUI to somehow generate a NewID() in such columns?

enter image description here

1 Answer 1

0

To my knowledge there is no way to do a INSERT INTO table (guid, val) VALUES (NEWID(), 1) style INSERT through the UI.

Maybe changing the table definition in such a way, that the guid column contains a DEFAULT(NEWID())? That way you can just ignore the column in the GUI. Although you have to hit Ctrl+R (Execute SQL) to see the guids that got genereated.

[Update 01/10/2023]

There is another way, using a VIEW. Create a view for the table selecting all the fields you want to insert. Then create an INSTEAD OF INSERT TRIGGER on this view and you can use the Edit top 200 GUI on the view to insert data into the table.

-- table for holding the data
CREATE TABLE dbo.dummy (guid UNIQUEIDENTIFIER NOT NULL, val INT NOT NULL);
GO

-- view for inserting purposes
CREATE OR ALTER VIEW dbo.dummy_view
AS
SELECT val
FROM   dbo.dummy;
GO

-- trigger that sets the guid values
CREATE TRIGGER dbo.dummy_insert
ON dbo.dummy_view
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.dummy (guid, val)
    SELECT NEWID(),
           Inserted.val
    FROM   Inserted;
END;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.