How to add an uniqueidentifier column and insert values

Question

Hi Friends,

Can any body please, give me some ideas here. I have a huge table with 189999 rows. I need to select only the middle values of it. How can I do it. I added unique identifier column. How do I insert the values in it. Please, suggest. i would really appreciate your helps. I have accepted the answers not. I did not know how to do it before. Thank you all

Answer 1

What database? What do you mean by "select only middle values"?

Given that you say "I added unique identifier column" I assume you use some version of MS SQL. You can generate new uniqueidentifier value using NEWID() funtion call.

Answer 2

To add a column of type UNIQUEIDENTIFIER with a default constraint and filling in the values right away, use something like:

ALTER TABLE dbo.Accounts
  ADD NewColumn UNIQUEIDENTIFIER
  CONSTRAINT DF_T_Account_NewColumn DEFAULT (newsequentialid()) WITH VALUES

If you already have your column in your table and just need to insert values, use:

UPDATE dbo.Accounts
SET NewColumn = newid()
WHERE NewColumn IS NULL

But I have no clue whatsoever what you mean by "selecting only the middle values" - please clarify.

Marc

Answer 3

This should work just fine.

UPDATE table
SET UniqueIdentifierColumn = NEWID()
WHERE ...

Notice that doing the update in a single set-based statement populates each row with a different GUID.

Sample Code

CREATE TABLE dbo.HugeTable (
    ColID       int     IDENTITY    PRIMARY KEY,
    ColGUID     uniqueidentifier,
    ColInt      int
)

DECLARE @ct     int

SET @ct = 0
WHILE @ct < 10 BEGIN
    SET @ct = @ct + 1
    INSERT INTO dbo.HugeTable (ColInt) VALUES (@ct)
END
GO

SELECT COUNT(*) AS Ct FROM dbo.HugeTable

UPDATE dbo.HugeTable
SET ColGUID = NEWID()
WHERE ColID BETWEEN 3 AND 7

SELECT * FROM dbo.HugeTable

Results

         Ct
-----------
         10

      ColID ColGUID                                   ColInt
----------- ------------------------------------ -----------
          1 NULL                                           1
          2 NULL                                           2
          3 E45E13D8-CFF0-4FC7-B7C9-1D53E95C502D           3
          4 33C3CCBC-B6BB-4CAA-AB10-338AA95F366E           4
          5 82136767-396E-4B33-B9DD-FFD30FCF4680           5
          6 EFA24EC9-F8F9-47CF-839F-D588F69D167F           6
          7 546F7C14-BDDA-4226-B45C-B0DDCD43E7DB           7
          8 NULL                                           8
          9 NULL                                           9
         10 NULL                                          10

Answer 4

Thanks all of you for your help. I really appreciate it.

Answer 5

read this article on uid's

UID's