Wednesday, 29 August 2012

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 = OFFSTATISTICS_NORECOMPUTE = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS =ONALLOW_PAGE_LOCKS = ONFILLFACTOR = 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
 
col1col2col3col4
1BritanLondonFootball
2WalesCardiffFootball
3ScotlandEdinburghFootball
4FranceParisFootball
5IrelandDublinFootball
6BelgiumBrusselsTennis
7GermanyBerlinTennis
8ItalyRomeTennis
9SpainMadridTennis
10SwissBernTennis
 
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
 
col1col2col3col4
1BritanLondonFootball
2WalesCardiffFootball
3ScotlandEdinburghFootball
4FranceParisFootball
5IrelandDublinFootball
6BelgiumBrusselsTennis
7GermanyBerlinTennis
8ItalyRomeTennis
9SpainMadridTennis
10SwissBernTennis
100BelgiumBrusselsTennis
101GermanyBerlinTennis
102ItalyRomeTennis
103SpainMadridTennis
104SwissBernTennis
 
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.

1 comment:

  1. Thank you for your work on this page. Very useful.

    ReplyDelete