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

Auto-increment fields when moving a base from Access to SQL server

Hello all,

I'm a total newbie with SQL Server 2000 and I have a little problem when
moving a database form Access 2000 to SQL Server 2000.
In the Access database, each table has an auto-increment field.
After importing the tables in SQL Server, all the auto-increment fields
are turned into "int" type fields.
Does anybody have an explanation for that mystery?

Thanks in advance,
YanYou might want to change the field type from int to identity. The identity
field type in SQL Server is analogous to the Autonumber field type in
Access.

good luck

CJ

"Yan Roosens" <yan.roosens@.skynet.be> wrote in message
news:3F7C4258.6C6B428E@.skynet.be...
> Hello all,
> I'm a total newbie with SQL Server 2000 and I have a little problem when
> moving a database form Access 2000 to SQL Server 2000.
> In the Access database, each table has an auto-increment field.
> After importing the tables in SQL Server, all the auto-increment fields
> are turned into "int" type fields.
> Does anybody have an explanation for that mystery?
> Thanks in advance,
> Yan|||Hi CJ,

> You might want to change the field type from int to identity. The identity
> field type in SQL Server is analogous to the Autonumber field type in
> Access.

Thank you, the error message related to that problem is no longer displayed, I
will now care about the next one :-(

Yan|||On Thu, 02 Oct 2003 17:20:57 +0200, Yan Roosens
<yan.roosens@.skynet.be> wrote:

>Hello all,
>I'm a total newbie with SQL Server 2000 and I have a little problem when
>moving a database form Access 2000 to SQL Server 2000.
>In the Access database, each table has an auto-increment field.
>After importing the tables in SQL Server, all the auto-increment fields
>are turned into "int" type fields.
>Does anybody have an explanation for that mystery?
>Thanks in advance,
>Yan
Because that isn't a data type. Look up identity for an explanation of
how sql server can provide that sort of functionality.

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
>

Auto-Increment

I'm creating a new SQL 2005 Express database and want to have a Customers
table. How do I auto-increment the CustomerID field? Programming in VB.Net
2005.
I'm new to SQL, so go easy on me please ;-)
TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================Check out IDENTITY in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Burtamus" <burtamus2003@.REMOVETHISyahoo.com> wrote in message
news:efRoosvYGHA.3392@.TK2MSFTNGP03.phx.gbl...
I'm creating a new SQL 2005 Express database and want to have a Customers
table. How do I auto-increment the CustomerID field? Programming in VB.Net
2005.
I'm new to SQL, so go easy on me please ;-)
TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================|||>> How do I auto-increment the CustomerID field [sic]? Programming in VB.Net2005.
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS.
What you want is a relational key that you can verify and validate.
Auto-incrementing has to do with the internal state of the hardware and
not the data model. This is why there are industry standard codes, why
websites use email addreses, etc.|||> Auto-incrementing has to do with the internal state of the hardware and
> not the data model.
And what about all that Date and Codd say on SURROGATE KEYS then?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145391251.621343.288390@.u72g2000cwu.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS.
> What you want is a relational key that you can verify and validate.
> Auto-incrementing has to do with the internal state of the hardware and
> not the data model. This is why there are industry standard codes, why
> websites use email addreses, etc.
>|||>> And what about all that Date and Codd say on SURROGATE KEYS then? <<
Codd defined them as created by the systrem, and NEVER exposed to the
user. Think of indexes or hashing as the same kind of creature. I
assume that Date feels the same way, but he seldom gets even that close
to implementation considerations.|||> Codd defined them as created by the systrem, and NEVER exposed to the
> user.
You DO NOT need to expose a SURROGATE KEY to a USER!!!

> Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
Thats your interpretation and not anybody elses. Its the view point of a
purist without regard for practical application of the logical model.
The use of the IDENTITY property as a SURROGATE KEY is fine so long as you
consider the implementation of your logical model specifically if the
database is in a distributed environment.
The use of surrogates with an application (note: the database is just one
component of your application) helps us get round many problems such as a)
performance, b) where the NATURAL KEY (note, there is no such thing as a
relational key) is composite and c) where the NATURAL KEY may or is prone to
changing which would cause dramatic concurrency and consistency problems.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145454973.944139.253670@.e56g2000cwe.googlegroups.com...
> Codd defined them as created by the systrem, and NEVER exposed to the
> user. Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
>|||--CELKO-- wrote:
> Codd defined them as created by the systrem, and NEVER exposed to the
> user. Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
I think you assume wrong. Date agrees with most of the rest of us, that
a surrogate key (like all keys) is part of the logical model. He says
so explicitly in Introduction to Database Systems and differentiates
them from tuple IDs, which are indeed an implementation feature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--