Manipulating Data

Question

Post reply

Manipulating Data

SQLEE

Ten Centuries

Points: 1077
More actions
January 9, 2008 at 8:02 am

#70691

Hello all,
I'm trying to generate a table from a source table and here is my logic:
IF len(PID) = 17 Then
1. insert the first 9 characters [Fisrt9] + [Next4]
2. subtract the next 4 characters [Next4] from the last 4 characters [Lastt4] to get a iCount
3. increment [First9]+[[iNext4] by 1 based of the iCount.
4. insert into same iTable
I created a temporary table tblKey and did an insert based on some of the logic above take a look:
create table tblKey(PIDKey varchar(13) not null, iCount int not null)
insert into tblKey(PIDKey, iCount)
select left(code,9) + substring(code,10,4), cast(right(code,4) as int) - cast(substring(code,10,4) as int)
from tblSource
where len(code)=17
Here is a look at my data from tblKey
PIDKey iCount
21027212100163
21027211400637
21027211400235
21027212400084
21027211400552
21027212400061
My question is how to I take the PIDKey let us say 2102721210016 and increment by 1 THREE times and insert into a final table? Can anyone help with some code. Thanks

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Have you tried casting PIDKEY to integer and then adding 3 to it? I'm not sure I see where the confusion is?

insert myfinaltable

select cast(pidkey as int)+3 as pidkey

from tblsource

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SQLEE Ten Centuries Points: 1077 More actions · Answer 2

Thanks SSCommitted,

May be I wasn't clear enough here. I may have to loop trough 3 time and increment by 1. So from 2102721210016 I will get the following:

2102721210016

2102721210017

2102721210018

2102721210019

I think I have to declare a variable and loop through, any help?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Use a Numbers table.

create table dbo.Numbers (Number int identity(0,1) primary key)

go

set nocount on

insert dbo.Numbers default values

go 10000

insert into finaltable (PK)

select cast(pidkey as int) + number

from dbo.Numbers

inner join dbo.tblKey

on number <= iCount

No loops, should do exactly what you want, should do it rapidly. (Keep the Numbers table after this. It has lots of other uses.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Aah... different issue. This can be fixed with a small tally table.

try this on

--perhaps make smalltally or Tally a permanent addition to the tool kit!

create table smalltally (n as int Primary key clustered)

--add some values - could go as high as you wish

insert smalltally

select 0 union all

select 1 union all

select 2 union all

select 3

--your new answer

insert myfinaltable

select cast(pidkey as int)+N

from tblsource, smalltally

Look ma - no loop!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SQLEE Ten Centuries Points: 1077 More actions · Answer 5

Thanks guys and specially GSquared,

The Numbers table worked perfectly for me but I want to have my leading zero back in the PK field. Like I explained above, I'm dealing with varchar 17 with leading zeros in some records.

insert into finaltable (PK, Code)

select cast(pidkey as bigint) + number, Code

from dbo.Numbers

inner join dbo.tblKey

on number <= iCount

Do I have to code something like: which means another table. Is there any better way? thanks much

select PID=

case

when len(PK)=12 then '0' + PK

else PK

end,

Code

from finaltable

Or may something like

update finaltable

set PK='0' + PK

where len(PK)=12

go

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

SQLElhassan (1/11/2008)
Thanks guys and specially GSquared,
The Numbers table worked perfectly for me but I want to have my leading zero back in the PK field. Like I explained above, I'm dealing with varchar 17 with leading zeros in some records.
insert into finaltable (PK, Code)
select cast(pidkey as bigint) + number, Code
from dbo.Numbers
inner join dbo.tblKey
on number <= iCount
Do I have to code something like: which means another table. Is there any better way? thanks much
select PID=
case
when len(PK)=12 then '0' + PK
else PK
end,
Code
from finaltable
Or may something like
update finaltable
set PK='0' + PK
where len(PK)=12
go

Back and forth, back and forth... and still not the answer you want. Save us all some time... read the following...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--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)