Creating sequential numbers

Question

Creating sequential numbers

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

One more question... Is it possible to add a single column to 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)

SQL33 SSCrazy Points: 2044 More actions · Answer 2

SQL33

SSCrazy

Points: 2044

October 25, 2006 at 8:57 pm

#668072

Absolutely

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Gimme ten minutes... I'll be right back...

--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: 1004318 More actions · Answer 4

Sorry... took a bit more than 10 minutes because of all the demo code I added but here's my suggestion... I used all temp tables so you could play without having to worry about real tables... and I needed to add a couple of columns to make this fish really swim...

Create a simple lookup table to hold the 5 digit branch number and other information like the branch name.
Modify your original table to hold the 3 columns I've identified in the example below... notice that I don't include the branch name in this table anymore... it's not necessary because of the table from 1 above. Also, the definition for the SEQ column has been changed from CHAR(14) to a formula... you will not be able to write to this column but it's MAGIC...

Here's all the code I tested with... read the comments for more info, please... if you study the code a bit, you'll find some obvious and some not so obvious tricks of the trade used to demo the table, the method, and the code... I recommend that you setup a foreign key to the Branch table from the yourtable and add some nonclustered indexes for whatever heavy duty queries you may write agains the two tables... most of the code below is just setting up for the demonstration... the real key is in the new columns of yourtable... write back if you have any questions...

--===== All of the tables below are just temp tables so you can "play".

-- This section of code makes sure they don't already exist.

IF OBJECT_ID('TempDB..#Branch') IS NOT NULL

DROP TABLE #Branch

IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

DROP TABLE #yourtable

--===== Create a table like this one... It holds the branch number and name...

-- I added some data to this just as a test...

CREATE TABLE #Branch

(

BranchNum CHAR(5) PRIMARY KEY CLUSTERED,

BranchName VARCHAR(30)

)

INSERT INTO #Branch (BranchNum,BranchName)

SELECT '32764','Main Office' UNION ALL

SELECT '00001','Corporate Headquarters' UNION ALL

SELECT '02345','Detroit Outlet'

--===== This is similar to what your previous table should look like

-- You can have as many columns as you'd like but we need these 3 at least

SET NOCOUNT ON

IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

DROP TABLE #yourtable

CREATE TABLE #yourtable

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --New column

BranchNum CHAR(5), --New column

SEQ AS BranchNum+REPLACE(STR(ID,9),' ','0') --New column, old name, is a "calculated column"

)

--===== Ok, let's create 10 rows for the main office...

SET ROWCOUNT 10

INSERT INTO #yourtable (BranchNum)

SELECT b.BranchNum

FROM #Branch b,

Master.dbo.SysColumns

WHERE b.BranchName = 'Main Office'

--===== and, 35 rows for the Detroit outlet...

SET ROWCOUNT 35

INSERT INTO #yourtable (BranchNum)

SELECT b.BranchNum

FROM #Branch b,

Master.dbo.SysColumns

WHERE b.BranchName = 'Detroit Outlet'

--===== ...and 7 rows for Corporate Headquarters

SET ROWCOUNT 7

INSERT INTO #yourtable (BranchNum)

SELECT b.BranchNum

FROM #Branch b,

Master.dbo.SysColumns

WHERE b.BranchName = 'Corporate Headquarters'

--===== Insert several records with different BranchNums

-- to simulate a general insert from some other table or file

SET ROWCOUNT 0

INSERT INTO #yourtable (BranchNum)

SELECT '32764' UNION ALL

SELECT '02345' UNION ALL

SELECT '32764' UNION ALL

SELECT '00001' UNION ALL

SELECT '32764'

--===== Display all records we entered

SET ROWCOUNT 0

SELECT y.ID, y.BranchNum, y.SEQ, b.BranchName

FROM #yourtable y,

#Branch b

WHERE y.BranchNum = b.BranchNum

ORDER BY ID

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

SQL33 SSCrazy Points: 2044 More actions · Answer 5

Thanks... worked fine. I enjoyed studying it and learned a lot from it... nice work...........

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

Thanks, Commando... I appreciate the feedback...

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