Create sequence

[Mel]

Hi SQL expert,

I have an existing table called "PropertyTbl" and I want to update the column PropertyID as sequence that start with int of 1001. How do write the sql statement to update this existing table and have the sequence column of PropertyID?

SELECT Property_ID, Rent_Or_Owned Peoperty_Age
FROM REAL_ESTATE_TBL

Property_ID	Rent or Owned	Property Age
5536	        Rent	             30
5684	        Rent	             45
2565	        Owned	             55
2353	         Owned	             65
2265	        Owned	             78
4875	         Owned	             5
1236	         Rent	             10
5568	        Owned	             15
5578	         Owned	             12
9868	        Rent	              3

Thank you all

[harishgg1]

hi

hope this helps

create sample data script

DROP TABLE IF EXISTS #DATA
CREATE TABLE #DATA(Property_ID INT ,Rent_or_Owned VARCHAR(20) , Property_Age INT )
INSERT INTO #DATA SELECT 5536 ,'Rent',30
INSERT INTO #DATA SELECT 5684 ,'Rent',45
INSERT INTO #DATA SELECT 2565 ,'Owned',55
INSERT INTO #DATA SELECT 2353 ,'Owned',65
INSERT INTO #DATA SELECT 2265 ,'Owned',78
INSERT INTO #DATA SELECT 4875 ,'Owned', 5
INSERT INTO #DATA SELECT 1236 ,'Rent',10
INSERT INTO #DATA SELECT 5568 ,'Owned',15
INSERT INTO #DATA SELECT 5578 ,'Owned',12
INSERT INTO #DATA SELECT 9868 ,'Rent', 3

SELECT * FROM #DATA

ALTER TABLE #DATA DROP COLUMN Property_ID

ALTER TABLE #DATA  ADD Property_ID INT IDENTITY(1001,1)

[Mel]

Hi Harish, Thank you for the response and the Identity solution but how do I create the sequence instead Identity?

[harishgg1]

hi

hope this helps

CREATE SEQUENCE SequenceCounter
    AS INT
    START WITH 1001
    INCREMENT BY 1;

SELECT NEXT VALUE FOR SequenceCounter,* FROM #DATA

[Mel]

Sweet! Thank you, Harish!