Set based Numbering process

Question

Set based Numbering process

mfink

SSCommitted

Points: 1632
More actions
October 25, 2011 at 3:31 pm

#149704

Declare @OriginalTable table(OT_ID int identity (1,1),
Rec_ID nvarchar(8),
RestofData nvarchar(20))
insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000001','aaaaaaaaaaaaaaaa')
insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000002','bbbbbbbbbbbbbbb')
insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000003','cccccccccccccccc')
insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000004','ddddddddddddddd')
insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000005','eeeeeeeeeeeeeee')
Declare @NewTable table(NT_ID int identity (1,1),
Rec_ID nvarchar(8),
RestofData nvarchar(20))
insert into @NewTable(Rec_ID,RestofData) (Select Null,'aaaaaaaaaaaaaaaa')
insert into @NewTable(Rec_ID,RestofData) (Select Null,'bbbbbbbbbbbbbbb')
insert into @NewTable(Rec_ID,RestofData) (Select Null,'cccccccccccccccc')
insert into @NewTable(Rec_ID,RestofData) (Select Null,'ddddddddddddddd')
insert into @NewTable(Rec_ID,RestofData) (Select Null,'eeeeeeeeeeeeeee')
------Select * From @OriginalTable
------Select * From @NewTable
Declare @Lapse_ID_Hold nvarchar(8)
Declare @Lapse_ID_Number int
Declare @MaxRec int
Declare @ThisRec int
Set @Lapse_ID_Number = (Select Right(Max(Rec_ID),7) from @OriginalTable)
Set @Lapse_ID_Number = (Select @Lapse_ID_Number + 1)
Set @Lapse_ID_Hold = (Select 'L' + convert(nvarchar(7), @Lapse_ID_Number ))
---Select @Lapse_ID_Hold,@Lapse_ID_Number
Set @MaxRec = (Select Max(ot_ID) From @OriginalTable)
Set @ThisRec = 1
While @ThisRec <= @MaxRec
Begin
Update @NewTable
Set Rec_ID = @Lapse_ID_Hold
where NT_ID = @ThisRec
--and Rec_ID is Null
/* Increase Lapse ID number when there is an updated record*/
Set @Lapse_ID_Number = @Lapse_ID_Number + 1
Set @Lapse_ID_Hold = (Select 'L' + convert(nvarchar(7),@Lapse_ID_Number ))
/* Run thru the table*/
Set @ThisRec = @ThisRec + 1
End
Select * From @OriginalTable
Select * From @NewTable
/*This will run thru a large number of records and runs slow. Can you show me how to create this code in set based
Thank you Mark@10wp.com*/

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

1. Did you use table variables in your post only to illustrate the data and these are NOT used in your actually code? (Yes used in my production DB. No NOT used in my production DB)

If the answer is NOT used in your production DB then read this article by Jeff Moden and see if it assists you.

http://www.sqlservercentral.com/articles/Performance+Tuning/62278/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I hope "RestOfData" isn't too wide for you to add an index to in both tables. Here's your original test data and a non-RBAR solution...

Declare @OriginalTable table(OT_ID int identity (1,1),

Rec_ID nvarchar(8),

RestofData nvarchar(20))

;

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000001','aaaaaaaaaaaaaaaa');

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000002','bbbbbbbbbbbbbbb');

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000003','cccccccccccccccc');

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000004','ddddddddddddddd');

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000005','eeeeeeeeeeeeeee');

Declare @NewTable table(NT_ID int identity (1,1),

Rec_ID nvarchar(8),

RestofData nvarchar(20))

;

insert into @NewTable(Rec_ID,RestofData) (Select Null,'aaaaaaaaaaaaaaaa');

insert into @NewTable(Rec_ID,RestofData) (Select Null,'bbbbbbbbbbbbbbb');

insert into @NewTable(Rec_ID,RestofData) (Select Null,'cccccccccccccccc');

insert into @NewTable(Rec_ID,RestofData) (Select Null,'ddddddddddddddd');

insert into @NewTable(Rec_ID,RestofData) (Select Null,'eeeeeeeeeeeeeee')

;

DECLARE @MaxRecID INT;

SELECT @MaxRecID = CAST(RIGHT(Rec_ID,2) AS INT)

FROM @OriginalTable

;

UPDATE new

SET Rec_ID = 'L' + RIGHT('0000000' + CAST(CAST(RIGHT(orig.Rec_ID,7) AS INT) + @MaxRecID AS VARCHAR(7)),7)

FROM @NewTable new

INNER JOIN @OriginalTable orig

ON new.RestOfData = orig.RestOfData

;

Select * From @OriginalTable;

Select * From @NewTable;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mfink SSCommitted Points: 1632 More actions · Answer 3

/* Basically I need to use the max(Rec_ID) + 1 in @OriginalTable to be the 1st number in the @NewTable

the RestofData cannot be used to join or matched on.

@OriginalTable and @NewTable are for this demo only.

They are actual real tables not declared*/

Declare @OriginalTable table(OT_ID int identity (1,1),

Rec_ID nvarchar(8),

RestofData nvarchar(20))

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000001','aaaaaaaaaaaaaaaa')

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000002','bbbbbbbbbbbbbbb')

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000003','cccccccccccccccc')

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000004','ddddddddddddddd')

insert into @OriginalTable(Rec_ID,RestofData) (Select 'L1000005','eeeeeeeeeeeeeee')

Declare @NewTable table(NT_ID int identity (1,1),

Rec_ID nvarchar(8),

RestofData nvarchar(20))

insert into @NewTable(Rec_ID,RestofData) (Select Null,'xoxoxoxoddddsdser')

insert into @NewTable(Rec_ID,RestofData) (Select Null,'vhgfjghgjh')

insert into @NewTable(Rec_ID,RestofData) (Select Null,'gfngngn')

insert into @NewTable(Rec_ID,RestofData) (Select Null,',nmbvnvcbdfthhrtrt5437686')

insert into @NewTable(Rec_ID,RestofData) (Select Null,'rtorhrepotrtpr')

------Select * From @OriginalTable

------Select * From @NewTable

Declare @Lapse_ID_Hold nvarchar(8)

Declare @Lapse_ID_Number int

Declare @MaxRec int

Declare @ThisRec int

Set @Lapse_ID_Number = (Select Right(Max(Rec_ID),7) from @OriginalTable)

Set @Lapse_ID_Number = (Select @Lapse_ID_Number + 1)

Set @Lapse_ID_Hold = (Select 'L' + convert(nvarchar(7), @Lapse_ID_Number ))

---Select @Lapse_ID_Hold,@Lapse_ID_Number

Set @MaxRec = (Select Max(ot_ID) From @OriginalTable)

Set @ThisRec = 1

While @ThisRec <= @MaxRec

Begin

Update @NewTable

Set Rec_ID = @Lapse_ID_Hold

where NT_ID = @ThisRec

--and Rec_ID is Null

/* Increase Lapse ID number when there is an updated record*/

Set @Lapse_ID_Number = @Lapse_ID_Number + 1

Set @Lapse_ID_Hold = (Select 'L' + convert(nvarchar(7),@Lapse_ID_Number ))

/* Run thru the table*/

Set @ThisRec = @ThisRec + 1

End

Select * From @OriginalTable

Select * From @NewTable

/*This will run thru a large number of records and runs slow. Can you show me how to create this code in set based

Thank you Mark@10wp.com*/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Why are you reposting your original post? Did you even try the code I posted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Ah... I see. You've changed the rules a bit.

Take the code I previously posted and make it match where OT_ID and NT_ID are equal instead of matching on RestOfData.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)