auto increment on a datawarehouse table help

Question

Post reply

auto increment on a datawarehouse table help

Goldfrapp

SSC Veteran

Points: 205
More actions
March 8, 2010 at 11:13 am

#216314

Hi
I am new to forums and would appreciate some help with this query?
I have a test table with three columns bookingid, itemid, itemno
i want to write a procedure so that the itemno rows autoincrements in a loop when the booking id column changes
bookingid, itemid, itemno
aaa , zzz, 1
aaa, yyy, 2
aaa, zzy, 3
bbb, zzz, 1
bbb, xxy, 2
ccc, xxx, 1
at the moment i have added the itemno column and set the value to null
but want to be able autoincrement this column when updates are sent to my table.
Many Thanks
Gra

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

A few questions to clarify the scenario:

What would be the sorting criteria? (I'd expect an ID or datetime column but there's none...)

How would you deal with an element being deleted? (e.g. aaa, yyy, 2) Would this require to renumber aaa, zzy to 2 or do you need to leave the gap?

Do you need to number it immediately during insert or can it be done separately? If the former: Will there be any issue with concurrent processes trying to insert rows at the same time?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Goldfrapp SSC Veteran Points: 205 More actions · Answer 2

Hi

Good questions.. Thanks for replying so promptly

The sorting order would be based on the bookingid.

It is a datawarehouse table so would only be recreated and populated once daily therefore the element of deleting a row would not matter as the numbering would renumber on each upload.

The renumbering can happen after the table is recreated as i could initially put the itemno as null.

Cheers again

Gra

Goldfrapp SSC Veteran Points: 205 More actions · Answer 3

ok trying to make it cleared... as its hard to explain without examples.

thanks again.

gra

At moment I have the booking key updating with a “null” value during upload but what I want to be able to do after the table is uploaded is to write a stored procedure to

order the table by bookingno and then to give the booking key an incremental value 1, 2, 3, 4 where bookingno is the same ( ie some groupby bookingid??)

bookingkeybookingnoitemnoitemtype

null1100FLIGHTS

null1101FLIGHTS

null1103HOTEL

null1104TRANSFERS

null2101FLIGHTS

null2106FLIGHTS

null2106TRANSFERS

null3107FLIGHTS

null3108FLIGHS

Eg. New table

The first 4 rows would have a bookingkey value of 1,2,3,4 the next 3 rows have a new bookingno therefore booking key starts

incrementing again and gives booking key values1,2,3

bookingkeybookingnoitemnoitemtype

11100FLIGHTS

21101FLIGHTS

31103HOTEL

41104TRANSFERS

12101FLIGHTS

22106FLIGHTS

32106TRANSFERS

13107FLIGHTS

23108FLIGHS

It would be some stored procedure to alter table and set the bookingkey

Based on a group by bookingno and autoincrement the bookingkey???

Any ideas?

LutzM SSC Guru Points: 107049 More actions · Answer 4

I'm still not sure what the correct order of the subnumbers will be, but anyway, I'd use ROW_NUMBER:

DECLARE @tbl TABLE

(

bookingkey int null,bookingno int, itemno int, itemtype varchar(20)

)

insert into @tbl(bookingno,itemno,itemtype)

SELECT 1, 100 ,'FLIGHTS' UNION ALL

SELECT 1, 101 ,'FLIGHTS' UNION ALL

SELECT 1, 103 ,'HOTEL' UNION ALL

SELECT 1, 104 ,'TRANSFERS' UNION ALL

SELECT 2, 101 ,'FLIGHTS' UNION ALL

SELECT 2, 106 ,'FLIGHTS' UNION ALL

SELECT 2, 106 ,'TRANSFERS' UNION ALL

SELECT 3, 107 ,'FLIGHTS' UNION ALL

SELECT 3, 108 ,'FLIGHS'

;with cte as

(

SELECT

bookingno,

itemno,

itemtype,

ROW_NUMBER() OVER(PARTITION BY bookingno ORDER BY itemno ) as row

FROM @tbl

)

update @tbl

set bookingkey=row

FROM cte

inner join @tbl t

on cte.bookingno = t.bookingno

and cte.itemno = t.itemno

and cte.itemtype = t.itemtype

SELECT *

FROM @tbl

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Goldfrapp SSC Veteran Points: 205 More actions · Answer 5

Awe thanks for that. Appreciate it. will gve it a go later 🙂