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.
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;
CREATE PROCEDURE [dbo].[uspGetSequentialGuid]
AS
DECLARE @SequentialGuids as Table ( SequentialGuid uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,InitDate datetime )
BEGIN
INSERT INTO @SequentialGuids(InitDate) Values(GETDATE());
END
SELECT SequentialGuid from @SequentialGuids
GO
CREATE PROCEDURE [dbo].[uspGetSequentialGuids](@RequiredGuids as int)
AS
DECLARE @SequentialGuids as Table ( SequentialGuid uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,InitDate datetime )
Declare @Counter int
SET @Counter = 0
While @Counter < @RequiredGuids
BEGIN
INSERT INTO @SequentialGuids(InitDate) Values(GETDATE());
SET @Counter = @Counter + 1
END
SELECT SequentialGuid from @SequentialGuids
GO
