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

Insert values in Primary key field

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

I've found the answer myself if anyone interested!

SET IDENTITY_INSERT table_name ON \g