T-SQL Sample with Dense_Rank


  

 CREATE TABLE #abc (abcId int, fiscalyr VARCHAR(10),incrementid INT,  city VARCHAR(20), state  VARCHAR(20), country  VARCHAR(20))

insert #abc (abcid, fiscalyr, incrementid, city, [state], country) values
(1, '1314', 1, 'Seattle' , 'WA', 'USA'),
(2, '1314', 1, 'Seattle' , 'WA', 'USA'),
(3, '1314', 2, 'Portland', 'OR', 'USA'),
(4, '1415', 3, 'Portland', 'OR', 'USA')

CREATE TABLE #def (defid int, fiscalyr  VARCHAR(20),  city  VARCHAR(20), [state]  VARCHAR(20), country  VARCHAR(20))

insert #def(defid, fiscalyr, city, [state], country) values
(1, '1314', 'Seattle', 'WA', 'USA'),
(2, '1516', 'Seattle', 'WA', 'USA')

;with mycte as (
select abcid, fiscalyr, incrementid, city, state, country, 1 as lvl
from #abc
union all
select defid, fiscalyr, null as incrementid, city, state, country , 2 as lvl
from #def)

,mycte2 as (
Select lvl, row_number() Over(Order by lvl, abcid) rn, fiscalyr,  city, state, country
 ,dense_rank()Over(Order by  fiscalyr,city desc,state,country ) incrementid
from mycte )

Insert INTO   #abc (abcid, fiscalyr, incrementid, city, state, country)

Select rn as abcid, fiscalyr,incrementid,  city, state, country
from mycte2
where lvl=2

select * from #abc

drop table #abc, #def

 

https://social.msdn.microsoft.com/Forums/en-US/0e8da07b-446f-4226-a976-c8bea66a9f0d/how-to-insert-with-increment-value?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s