need to use the max(Rec_ID) + 1 in @OriginalTable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need to use the max(Rec_ID) + 1 in @OriginalTable

Author

Topic

MarkFi
Starting Member

1 Post

Posted - 2011-10-27 : 13:12:35

/* Basically I 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*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 13:19:57

just do an update like below and check


....

update t
set t.Rec_ID='L'+ cast(t.Rn + (select max(replace(Rec_ID,'L',''))  FROM @OriginalTable)as varchar(25))
from
(select row_Number() over (order by NT_ID) AS rn,Rec_ID
from @NewTable)t


Select * From @OriginalTable

Select * From @NewTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 14:10:25

Drop the column and re-add the column?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources