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.
Thank you for your work on this page. Very useful.
ReplyDeletetks works !!!
ReplyDeleteI thank you for the information and articles you provided
ReplyDeleteThis 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
ReplyDeleteThere 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