0

I want to insert guid values into an existing column called rowguid in an existing table.

I've created the column with this statement:

ALTER TABLE [myschema].[mytablename] 
    ADD rowguid uniqueidentifier

The column has been created and it's full of NULLs.

I've tried this to generate the IDs for every row:

INSERT INTO [myschema].[mytablename] (rowguid) 
VALUES(NEWID())

But this returns with:

Cannot insert the value NULL into column '[another column name in my table]', table '[database].[schema].[mytablename]'; column does not allow nulls. INSERT fails. The statement has been terminated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mons
  • 1
  • 3
    The error complains about NULLs, not UNIQUEIDENTIFIER. I suspect the table has other non-nullable columns that need to be set as well – Panagiotis Kanavos Jan 10 '23 at 11:22
  • Yes, the table has other non-nullable columns, but I don't understand what needs to be set. – Mons Jan 10 '23 at 11:23
  • 2
    Those columns. You can't insert a new row without filling *all* non-nullable columns. `INSERT ... VALUES` inserts a single row. You need to include all non-nullable columns in the column list and provide values for all of them in the `VALUES` clause – Panagiotis Kanavos Jan 10 '23 at 11:24
  • 2
    `INSERT` doesn't update the value of a row, that's what `UPDATE` does. Though you would have been better off adding the column with a `DEFAULT` `CONSTRAINT` and using `WITH VALUES` (or probably defining it as `NOT NULL`) if you didn't want the column to be populated with `NULL` values. – Thom A Jan 10 '23 at 11:24
  • 1
    If the *real* question is how to modify the existing rows, use UPDATE, not INSERT, eg `UPDATE thatTable SET ThatColumn=NEWID()` – Panagiotis Kanavos Jan 10 '23 at 11:25
  • Does this answer your question? [Efficient way to update all rows in a table](https://stackoverflow.com/questions/2635689/efficient-way-to-update-all-rows-in-a-table) – Thom A Jan 10 '23 at 11:28
  • Does this answer your question? [In SQL, How to add values after add a new column in the existing table?](https://stackoverflow.com/questions/31807768/in-sql-how-to-add-values-after-add-a-new-column-in-the-existing-table) – Thom A Jan 10 '23 at 11:28
  • You can create a new *non*-nullable row by specifying a `DEFAULT` value. This way you can add `rowguid` and fill it at once **BUT** why do you want this? A GUID is a very bad choice for a PRIMARY or any kind of key. They're bigger than eg `bigint`, can't be used for range queries and cause table fragmentation. You can use `NEWSEQUENTIALID()` to alleviate fragmentation but you still end up wasting space. – Panagiotis Kanavos Jan 10 '23 at 11:28
  • Yes, that is the real question. Problem solved. Thank you so much! – Mons Jan 10 '23 at 11:28
  • @Mons why do you want a GUID column at all? Especially one named `rowguid`? A row's identity is its PK. A `rowguid` may make sense in a replication scenario but even there, there may be better options. Unless you use NEWSEQUENTIALID even replication will be impacted - unless you use another feature like Change Tracking – Panagiotis Kanavos Jan 10 '23 at 11:29

0 Answers0