adding sequences to a values in a column

Question

Post reply

adding sequences to a values in a column

Isabelle2378

SSCertifiable

Points: 6174
More actions
April 30, 2007 at 5:06 pm

#370969

Hi,
I have a column that has values:
61205
60606
20712
and I need to create a new column that has the values concatinated with a sequence at the end so the new column would look like:
61205     612050001
60606     606060002
20712     207120003
In Oracle, I would create a sequence and a trigger that would populate it on insert, create the new column and then set new_column= converted_lot || LPAD(to_char(substr(seq_no,2,4)),4,'0'). I've been looking for examples and reading BOL but can't find anything that talks about sequences in respect to what I'm trying to do.
Any help would be appreciated!
Thanks,
Isabelle
Thanks!
Bea Isabelle

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SELECT Col1, 10000 * Col1 + ROW_NUMBER() OVER (ORDER BY Col1 DESC) AS Col2

ORDER BY Col1 DESC

N 56°04'39.16"
E 12°55'05.25"

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

Peter's method is absolutely a great way to take care of existing data. The problem is, wadaya gonna do with new data?

If you really need to do such a thing, my recommendation is to add an IDENTITY column and a calculated column to concatenate the Col1 and IDENTITY columns. That way, the impact on existing code is nil.

Formula for the concatenated column should be something like this (following your example)...

RIGHT('00000'+CAST(Col1 AS VARCHAR(5)),5)+RIGHT('0000'+CAST(identitycol AS VARCHAR(4)),4)

Sure, there are tricks you can do with STR and REPLACE, but the code above remains deterministic so you can put an index on the column if you need to.

I'm really curious about this... why do you need to do such a thing? The reason I ask is because you've limited table to a mere 9999 entries...

[Edit] Oh yeah... almost forgot... if this is just for display purposes, do it in the app.

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

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 3

Hi,

I'm sorry, but I forgot that this is a SQL 2000 database (we have both 2000 and 2005 and I got them confused) so when I try to use the row_number, I get the error:

Server: Msg 195, Level 15, State 10, Line 2

'ROW_NUMBER' is not a recognized function name.

Any suggestions for SQL 2000? This is only a one time thing. We are converting to a new ERP system and I need to move our legacy inventory information over and create a new lot number by taking the old lot code and adding a sequence to it so that every record is unique. Moving forward, the new EPR system will take care of generating that.

Thanks!

Isabelle

Thanks!
Bea Isabelle

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

If this is a SQL Server 2000 problem, why do you post in a SQL Server 2005 forum?

DECLARE @Stage TABLE (RecID INT IDENTITY, ID INT)

INSERT @Stage (ID)

SELECT Col1 FROM Table1 ORDER BY Col1 DESC

UPDATE t1

SET t1.ID = 10000 * s.ID + s.RecID

FROM Table1 AS t1

INNER JOIN @Stage AS s ON s.ID = t1.ID

N 56°04'39.16"
E 12°55'05.25"

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 5

As I said, since we have both 2000 and 2005, I got confused and forgot this was in our SQL 2000 database. Sorry.

I actually created an identity column which created sequence numbers and used the following:

SET MOVEX_LOT_NO = SUBSTRING([conv_lot_no],1,6) + RIGHT('0000' + CONVERT(varchar, sequence), 4)

Thanks for all the info and help.

Isabelle

Thanks!
Bea Isabelle

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

Like I said, that takes care of existing data... whatcha gonna do for new data that enters into the table?

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

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 7

Any new inventory item entered into the system will get the number generated automatically by the application. This is just a one time deal to get in our inventory from our old system into the new ERP.

Thanks!

Thanks!
Bea Isabelle