Autoincrement record position in a view

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@.hotmail.com

Thank you

Christos StavrinouWith FoxPro you can use the RECNO() function:

SELECT RECNO() AS recno, persons FROM myTable

This is not possible with SQL Server, and I am relatively sure it is not possible with Access.

Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541

Terri

autoincrement question sql 2005 server

How add column autoincrement in sql server 2005 use SQL Server Management
Studio?

Tominfo (informatyk@.fitness[CUT]authority.pl) writes:

Quote:

Originally Posted by

How add column autoincrement in sql server 2005 use SQL Server Management
Studio?

For an existing column? That's a bit of work, because you need to copy
the table to a new definition. There is now ALTER TABLE command to add/drop
identiity. It may look simple in SSMS, but Mgmt Studio performs all that
drop affair behind your back. That would be OK if it knew how to do it,
but it doesn't, so if things go wrong you may mess up your database.

Of course, if you run SQL commands, you may mess the database on your
own as well, but at least you knew what you did.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

Autoincrement in varchar value

hi Guys,
I've tangled in serious problem,
Is there any way in which we can autoincrement in
varchar value like i've one column in my table called PayCardId that should
be in 9 digit say '900001@.@.@.', now requirement is this to increment in same
format but it should automatically save in Database, for example
900001@.@.@.
.
.
900100@.@.@.
900101@.@.@.
900102@.@.@.
When it completes it's hundred series it should come in
thousand series like 901001@.@.@. and then
901002@.@.@.
901003@.@.@.
so on and so for
I'm sure there should be any way to
increment this, but i'm not able to think it this time, Please help me ASAP
Any help would be appriciated
ThanksHi
I am not sure whether this code will help you. if this is the logic. u can
cast the final value as string and insert into database.
CREATE TABLE #TempTable (CustID VARCHAR(9))
INSERT INTO #TempTable (CustID) VALUES ('900001ABC')
INSERT INTO #TempTable (CustID) VALUES ('900002XYZ')
INSERT INTO #TempTable (CustID) VALUES ('900003ABC')
DECLARE @.intNewValue INT
SELECT @.intNewValue = CAST(LEFT(MAX(CustID),6) AS INT) FROM #TempTable
SELECT @.intNewValue
SET @.intNewValue = @.intNewValue + 1
SELECT @.intNewValue
Thanks,
Ciju
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||Thanks a lot Ciju, it was realy helpful for me, i've got a good idea from
this code
thanks a lot again for giving quick response,
may god bless you
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||If you can touch on the table design yet..
then try this.
create TABLE [char_increment] (
[a] AS cast(id_num as varchar) + '@.@.@.',
[id_num] [bigint] IDENTITY (900000, 1) NOT NULL ,
[fname] [varchar] (20)
)
insert into char_increment (fname)
values ('a')
insert into char_increment (fname)
values ('b')
insert into char_increment (fname)
values ('c')
insert into char_increment (fname)
values ('d')

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
Check out SCOPE_IDENTITY() in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***Check out SCOPE_IDENTITY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.developersdex.com ***Check out SCOPE_IDENTITY() in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.developersdex.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.developersdex.com ***

AutoIncrement Fields

I am trying to upsize a Microsoft Access database.
I have used Autoincrement/random to generate some key fields
Is there a way to duplicate this fuctionality in MSDE? ( I can find the
ability to increment starting with a seed)
thanks
Ed Warren.
Hi,
See IDENTITY property in SQL Server books online. Usage is
CREATE TABLE TESTTABLE(i int IDENTITY(1,1), Name Varchar(10))
So the value for i start with 1 and increment by 1
Thanks
Hari
SQL Server MVP
"Ed Warren" <eowarren@.fakeaddress.zzz> wrote in message
news:exV1d5rNFHA.1500@.TK2MSFTNGP09.phx.gbl...
>I am trying to upsize a Microsoft Access database.
> I have used Autoincrement/random to generate some key fields
> Is there a way to duplicate this fuctionality in MSDE? ( I can find the
> ability to increment starting with a seed)
> thanks
> Ed Warren.
>
|||Ed,
You can use a trigger to give you a random auto increment - the upsizing
wizard can do this for you or you can add your own similar to
CREATE TRIGGER [StudentsTrig] ON dbo.Students
FOR INSERT
AS
SET NOCOUNT ON
Declare @.randc int, @.newc int
SET @.randc=0
WHILE @.randc=0
BEGIN
SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
END
SELECT @.newc = (SELECT [Stud ID] FROM inserted)
UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
You can modify the select statement if you want to allow negative numbers
(like Access).
Russ Stevens
|||That's what I'm looking for, thanks a lot
Ed Warren
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:e3FZJAtNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Ed,
> You can use a trigger to give you a random auto increment - the upsizing
> wizard can do this for you or you can add your own similar to
> CREATE TRIGGER [StudentsTrig] ON dbo.Students
> FOR INSERT
> AS
> SET NOCOUNT ON
> Declare @.randc int, @.newc int
> SET @.randc=0
> WHILE @.randc=0
> BEGIN
> SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
> END
> SELECT @.newc = (SELECT [Stud ID] FROM inserted)
> UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
> You can modify the select statement if you want to allow negative numbers
> (like Access).
> Russ Stevens
>

Autoincrement

Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,

> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>