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...exec doesn't work properly

Hello
MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
I have certain procedure which can't be reproduced
using my common technique.
CREATE table #t (text nvarchar(4000))
insert into #t exec sp_helptext 'objectname'
SELECT * FROM #T
DROP TABLE #T
exec sp_helptext 'objectname'
Two output resultsets are DIFFERENT!
Row order in temporary table doesn't coincide with real
row order (two rows with numbers 119 and 120 inserted
into positions 68 and 69).
This is critical for me!
Does anyone know the cause of this or any workaround?
Thanks,
Serge ShakhovThere is no way to totally control how rows are stored in a table. The
order in which you insert them has little to do with how they are stored or
placed into the pages on disk. Even with a clustered index you are not
guaranteed for everything to be physically and logically in order. So if
you want to get them out of a table in a specific order then you need to
specify an ORDER BY clause.
--
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:48dgmb.5o3.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
> Row order in temporary table doesn't coincide with real
> row order (two rows with numbers 119 and 120 inserted
> into positions 68 and 69).
> This is critical for me!
> Does anyone know the cause of this or any workaround?
> Thanks,
> Serge Shakhov
>|||Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
I still don't know the reason of this behavior
but now I know that changing nvarchar datatype
to varchar forces server to work properly.
This can be considered as workaround.
Serge Shakhovsql