insert auto incrementing column value/id

Question

I need an auto-incrementing id which I don't in the code below when it inserts into the table every few seconds.

Currently the id column is null.

Any ideas how to do that?

INSERT INTO [display].[dbo].[data1] (position, action, sync)
    SELECT
        CAST(RAND() * 3 AS INT) + 1, 
        CASE CAST(RAND() * 8 AS INT) 
            WHEN 0 THEN 'Weighlifting'
            WHEN 1 THEN 'Shoutout'
            WHEN 2 THEN 'dumbbells'
            WHEN 3 THEN 'facewipe'
            WHEN 4 THEN 'pacman'
            WHEN 5 THEN 'shootingstar'
            WHEN 6 THEN 'tornado'
            WHEN 7 THEN 'muscle' 
         END,
         CAST(RAND() * 4 AS INT)
         WAITFOR DELAY '00:00:01.500'
GO 3000

Before:

id  position        action     sync
-----------------------------------
NULL    3           facewipe    2         
NULL    1           tornado     1         
NULL    1           tornado     3         
NULL    1           Shoutout    3         
NULL    2           NULL        0         
NULL    1           dumbbells   2         
NULL    2           Shoutout    2     

Expected table:

id  position        action    sync
-----------------------------------
1     3             facewipe    2         
2     1             tornado     1         
3     1             tornado     3         
4     1             Shoutout    3         
5     2             NULL        0         
6     1             dumbbells   2         
7     2             Shoutout    2     

Answer 1

Declare the data type as IDENTITY of id column.

ALTER TABLE dbo.YourTable DROP COLUMN ID;
ALTER TABLE dbo.YourTable ADD ID INT IDENTITY;

This will add increment no for the existing rows.

Alternative way to update existing rows:

DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1