CODEWITHSUNDEEP
sqlsql-server
Rodrigo Alves
Rodrigo Alves

Reputation: 35

How to manually populate null values without using the 'identity' command in an SQL table

I created the table 'test': 

create table test  
(  
    column1 varchar(10),  
    column2 varchar(10),  
)

and added the values

insert into test values('value1','value2')
insert into test values('value1','value2')

But now I need to create a column that will be a primary key, but I can not use the 'Identity' command because the control will be done by the application.

alter table test add ID int

How do I populate values that are null so they stay in sequence? Where as they are null.

result from 'select * from test':

column1 column2 ID

value1 value2 NULL
value1 value2 NULL

Upvotes: 1

Views: 1076

Answers (4)

Lostblue
Lostblue

Reputation: 419

In the first place, you can't create a PK with NULL values (check MSDN here).

If you want to make your application to create your PK value, you have to give it at INSERT time or give some "Default" value(s) before your application edit it. The second option is dangerous for various reasons (trust your application to ensure unicity? how will you make a lot of INSERT in a short time? etc).

Upvotes: 0

Backs
Backs

Reputation: 24903

Add temp identity column, copy values from this column to ID, then drop temp column:

CREATE TABLE #test
(
    column1 varchar(10),
    column2 varchar(10),
)

INSERT INTO #test
VALUES
('aa','aa'),
('bb','bb')

ALTER TABLE #test 
    ADD ID INT

ALTER TABLE #test 
    ADD TempID INT IDENTITY(1,1)

UPDATE t
SET
    t.ID = t.TempID
FROM #test t

ALTER TABLE #test
DROP COLUMN TempID  

SELECT *
FROM #test t

DROP TABLE #test

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

You can do this:

  1. add a nullable column Id
  2. update Id with a value
  3. set Id as not null
  4. make Id the primary key. 
create table test (
    column1 varchar(10)
  , column2 varchar(10)
);

insert into test values
 ('value1','value2')
,('value1','value2');

alter table test add Id int null;

update t set Id = rn 
  from (
    select *
      , rn = row_number() over (order by column1, column2)
    from test
  ) as t;

alter table test alter column Id int not null;

alter table test
  add constraint pk_test primary key clustered (Id);

select * from test;

test setup: http://rextester.com/DCB57058

results: 

+---------+---------+----+
| column1 | column2 | Id |
+---------+---------+----+
| value1  | value2  |  1 |
| value1  | value2  |  2 |
+---------+---------+----+

Upvotes: 2

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Try this

;WITH cte
AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY column1, column2) AS RowNum FROM test
)
UPDATE cte
    SET iID = RowNum

Now check your table records

SELECT * FROM test

Upvotes: 4

Related Questions