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.

5 comments:

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

    ReplyDelete
  2. I thank you for the information and articles you provided

    ReplyDelete
  3. This loan combines a fixed price with an adjustable rate. mortgage payment calculator canada Great service is just around the corner with helpful mortgage information and valuable resources to make buying your home quick and easy. canadian mortgage calculator

    ReplyDelete
  4. There are numerous reasons why it's imperative to check Social Security number data on your representatives, for example, to ensure that you are recruiting legitimate laborers, guaranteeing precise compensation reports, and guaranteeing that your workers' wages are appropriately credited to their SSA income records. A basic grammatical mistake could have sad long haul impacts on a representative. For instance, consider the possibility that a worker's SSN was entered erroneously. Imagine a scenario in which nobody saw this for quite a long time. A basic confirmation could stay away from this possibly terrible bungle.click here

    ReplyDelete