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
ReplyDeletetakipciadresin.com - instagram takipçi satın al - takipcialdim.com - instagram takipçi satın al - smmpaketleri.com - tiktok takipçi satın al - instagram beğeni satın al - otomatikbegenisatinal.com - adresekleme.com - btcturk güvenilir mi - bitcoinhesabiacma.com - izlenme-satin-al.com - numarasmsonay.com - borsagazete.com - takipcisatinals.com - youtube izlenme satın al - google haritalara yer ekleme - altyapisizinternet.com - mikrofiber havlu - no deposit bonus forex 2021 - tiktok jeton hilesi - tiktok beğeni satın al - microsoft word indir - misli apk indir - binance güvenilir mi - binance güvenilir mi - binance güvenilir mi - guvenilirmiyasalmi.com - takipçi satın al - kimlik kaybetme cezası - engelli emekli maaşı hesaplama - sigorta için gerekli evraklar - ptt kart bakiyesi sorgulama - asker yol parası sorgulama - kapıda ödeme kargo gönderme - aile hekimi maaşları - esnaf odası kayıt ücreti - bankaların pos cihazı komisyon oranları - mikrofiber havlu
ReplyDeletetakipçi satın al
ReplyDeletetakipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
takipçi satın al
instagram takipçi satın al
instagram takipçi satın al
takipçi satın al
takipçi satın al
instagram takipçi satın al
instagram takipçi satın al
instagram takipçi satın al
instagram takipçi satın al
takipçi satın al
instagram takipçi satın al
marsbahis
ReplyDeletebetboo
sultanbet
marsbahis
betboo
sultanbet
TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com
ReplyDeleteHello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com
Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.
Nice & Informative Blog ! We offer welcome to yorkie puppies nearme. Check it out!...
ReplyDeletepuppies for sale near me
yorkie puppies with home training
where to buy Yorkie
Yorkie Female Puppies for sale
Yorkie puppies ready for their forever homes
The best protection against identity theft, without doubt, is identity theft prevention. A recent article in USA Today shows how identity theft can happen. An identity theft ring hacked into Marshall Department Store's main computer and stole thousands of credit card numbers. The ring members then traveled throughout Florida using these stolen credit card numbers to charge high-value merchandise at stores like WalMart. best fake id
ReplyDeleteThank you so much! this post really resolve a big issue I am handling now!
ReplyDelete