Inserting a block of numbers sequentially into a table

I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7, bu
t
don't have the experience to understand this enough. I also looked in
previous questions on this group, but find I still need help. I need to
insert a series of MSR (Medical Service Record) numbers into a table of
appointments over a selected date range. The last used MSR is recorded in
this table in column LastMSR:
CREATE TABLE [UserVars] (
[LastMSR] [int] NOT NULL ,
[RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
Beginning with LastMSR+1, I want to insert numbers sequentially into the
column MSR of the Appointments table (see below), selecting rows where MSR =
0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
CREATE TABLE [Appointment] (
[APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[APPT_DATE] [datetime] NULL ,
[RESOURCE_ID] [int] NOT NULL ,
[DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[START_TIME] [datetime] NULL ,
[DURATION] [datetime] NULL ,
[STATUS] [smallint] NOT NULL ,
[CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSR] [int] NOT NULL ,
[ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
DEFAULT (getdate()),
[ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Once I'm done, I want to write my highest assigned MSR back to table
UserVars; however, I don't want any other user to allocate a block of MSRs
until I'm done.
Thank you...My first question is: Do you want to single thread access to the table? In
2000, do something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
In 2005, look at rownumber()
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7,
>but
> don't have the experience to understand this enough. I also looked in
> previous questions on this group, but find I still need help. I need to
> insert a series of MSR (Medical Service Record) numbers into a table of
> appointments over a selected date range. The last used MSR is recorded in
> this table in column LastMSR:
> CREATE TABLE [UserVars] (
> [LastMSR] [int] NOT NULL ,
> [RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> ) ON [PRIMARY]
> GO
> Beginning with LastMSR+1, I want to insert numbers sequentially into the
> column MSR of the Appointments table (see below), selecting rows where MSR
> =
> 0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
> CREATE TABLE [Appointment] (
> [APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [APPT_DATE] [datetime] NULL ,
> [RESOURCE_ID] [int] NOT NULL ,
> [DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [START_TIME] [datetime] NULL ,
> [DURATION] [datetime] NULL ,
> [STATUS] [smallint] NOT NULL ,
> [CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSR] [int] NOT NULL ,
> [ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
> DEFAULT (getdate()),
> [ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> Once I'm done, I want to write my highest assigned MSR back to table
> UserVars; however, I don't want any other user to allocate a block of MSRs
> until I'm done.
> Thank you...|||Sorry, I don't understand. When I start, the appointments table might be
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 0
2 11/07/2005 0
etc.
If the last used MSR number (stored in UserVar table) is 20, then when I'm
done I want the appointments table to look like this:
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 21
2 11/07/2005 22
etc.
Starting with the stored LastMSR+1, each row increases MSR by 1.
"Louis Davidson" wrote:

> My first question is: Do you want to single thread access to the table?
In
> 2000, do something like:
> select 'Blue' as color
> into #testtable
> union all
> select 'Red'
> union all
> select 'Green'
> select color, (select count(*) from #testTable as t2 where t2.color <=
> #testTable.color) as rowNumber
> from #testTable
> order by 2
> In 2005, look at rownumber()
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>
>