How to use Row_number() to insert consecutive numbers (Identity Values) on a Non-Identity column

Scenario:-

We have database on a vendor supported application in which a Table TestIdt has a integer column col1 which is not specified as identity as below,

 

--Create Table

CREATE TABLE [dbo].[TestIdt]

(

[col1] int NOT NULL,

[col2] [varchar](60) NULL,

[col3] [varchar](60) NULL,

[col4] [varchar](50) NULL,

CONSTRAINT [PK_TestIdt] PRIMARY KEY CLUSTERED

(

[col1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

 

--Insert Values to the Table

INSERT INTO TestIdt VALUES (1,'Britan','London','Football')

INSERT INTO TestIdt VALUES (2,'Wales','Cardiff','Football')

INSERT INTO TestIdt VALUES (3,'Scotland','Edinburgh','Football')

INSERT INTO TestIdt VALUES (4,'France','Paris','Football')

INSERT INTO TestIdt VALUES (5,'Ireland','Dublin','Football')

 

Now we need to insert values of another table(subsetTbl as below) to TestIdt with col1 on TestIdt need to be increaed by 1 with each row in the subset, In short Col1 should be populated like an identity column.

 

CREATE TABLE [dbo].[subsetTbl]

(

[subcol2] [varchar](60) NULL,

[subcol3] [varchar](60) NULL,

[subcol4] [varchar](50) NULL,

) ON [PRIMARY]

 

--Insert Values to the Table

INSERT INTO subsetTbl VALUES ('Germany','Berlin','Tennis')

INSERT INTO subsetTbl VALUES ('Swiss','Bern','Tennis')

INSERT INTO subsetTbl VALUES ('Italy','Rome','Tennis')

INSERT INTO subsetTbl VALUES ('Belgium','Brussels','Tennis')

INSERT INTO subsetTbl VALUES ('Spain','Madrid','Tennis')

 

Let us see how we can Accomplish this task, One way of doing it is using SQL cursors which looks fine till we have small tables but with large tables and inserts cursor has its own disadvantages of using more resources and

more locking on the table. With the introduction of OVER clause on SQL 2005 we can accomplish this task pretty easily using rownumber function as below,

 

DECLARE @cnt int

SELECT @cnt = MAX(col1) from TestIdt

    INSERT INTO TestIdt

    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl

 

col1	col2	col3	col4
1	Britan	London	Football
2	Wales	Cardiff	Football
3	Scotland	Edinburgh	Football
4	France	Paris	Football
5	Ireland	Dublin	Football
6	Belgium	Brussels	Tennis
7	Germany	Berlin	Tennis
8	Italy	Rome	Tennis
9	Spain	Madrid	Tennis
10	Swiss	Bern	Tennis

 

Now that we have inserted subsetTbl values to TestIdt with Col1 values incremented by 1, We can specify the start value as we wish, Let say I want to start with 100 for next insert We can do it as follows,

 

DECLARE @cnt int

SET @cnt = 99

    INSERT INTO TestIdt

    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl

 

col1	col2	col3	col4
1	Britan	London	Football
2	Wales	Cardiff	Football
3	Scotland	Edinburgh	Football
4	France	Paris	Football
5	Ireland	Dublin	Football
6	Belgium	Brussels	Tennis
7	Germany	Berlin	Tennis
8	Italy	Rome	Tennis
9	Spain	Madrid	Tennis
10	Swiss	Bern	Tennis
100	Belgium	Brussels	Tennis
101	Germany	Berlin	Tennis
102	Italy	Rome	Tennis
103	Spain	Madrid	Tennis
104	Swiss	Bern	Tennis

 

We have eventually created a Identity Insert for a Non-Identity Column.

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.