How to use NEWSEQUENTIALID() after a table has already been created and has data?

Question

Could anyone help me with this please. I have a table that's not unique (as I'm rummaging through old databases of my predecessor.)

I would like to assign it to the "ID" field within the Fruits table I have.

I'd like to go ahead and get the NEWSEQUENTIALID() to be setup so I can see all what I'm working with.

Answer 1

Assuming ID is of type uniqueidentifier, you can create another column with sequential guids as default.This will populate the values in that column. After that you may copy these values to your id column and then drop the tmp column. Once all data is in, then specify defaults for your id column. See SQL Script below :

--create a new column with default as sequential ids

USE [BASKET]
ALTER TABLE [FRUITS]
ADD  [TMPID] UNIQUEIDENTIFIER NOT NULL  CONSTRAINT DF_TMPID DEFAULT NEWSEQUENTIALID()
GO

--update existing id column values with the newly created values

UPDATE [FRUITS] SET ID = TMPID GO

--remove constraint

ALTER TABLE [FRUITS] DROP CONSTRAINT DF_TMPID GO

--remove the temp column

ALTER TABLE [FRUITS] DROP COLUMN TMPID GO 

--specify defaults for newly inserted defaults

ALTER TABLE [FRUITS]  ADD DEFAULT NEWSEQUENTIALID() FOR ID

--or--

ALTER TABLE [FRUITS] ADD CONSTRAINT DF_ROWGUID DEFAULT NEWSEQUENTIALID()  FOR ID;