Hi All:
I have a column in a table with datatype varchar(60). I need to start inserting values into this column (starting sequentially from R000001 onwards). So, the next record would be R000002, so on and so forth. I tried using NewID() function, however this generates unique hexadecimal numbers like 019D9F4F-E16C-4EDF-9E02-DF1190B7AECF

Any help is greatly appreciated

Create a func with a virtual column and at each insert take the max of that colmn and do +1. And concat with R.

Howz it.............

 

If you mean this...

http://www.cs.rpi.edu/~sibel/dbs/FALL2003/system_info/oracle/unique.htm

How to generate a unique number (SEQUENCE)?

Oracle has solved a problem of assigning unique numbers (i.e.: studentID) without having to create a special table and handle update an concurrence it by using CREATE SEQUENCE:

In SQL*PLUS create a sequence generator

CREATE   SEQUENCE   sID
INCREMENT   BY   1
START   WITH   100
ORDER;

This will create a sequence that can be accessed by insert and update statements. Typically, the sequence is created with a statement like the following.

For sqlplus to insert into a table.

INSERT   INTO   student   (studentID, name, address)
values   (sID.NextVal, 'Albert', '123, Sage Ave., NY. 12180');

The NextVal attached to studentID tells Oracle you want the next available sequence number from the studentID sequence. This is guaranteed to be unique.

To use the current number (i.e., the same number more than once), CurrVal is used instead of NextVal.

How about creating output with identity value and then concatenate and insert to the column.

thanks

Thanks to who answered. But I am still confused. Can you please show me a dummy function?

Ask SQL Guru RawBee

Suman Anwar Suman

we can do that several ways...here is one simple idea for getting newid. you can use it as function or can just modify codes to get ur desire result.

 

DECLARE @LastID AS VARCHAR(60) --R000001

DECLARE @NewID AS INT

SET @LastID = (SELECT top 1 SequenceID FROM test ORDER BY SUBSTRING(SequenceID,2,60) DESC) -- Gets LastId in Column

SELECT @NewID =(LTRIM(MAX(SUBSTRING(SequenceID,2,60)+1))) FROM test -- Gets only Numeric Part and Increment by 1

SELECT REPLACE(@LastID,RIGHT(@LastID,LEN(@NewID)),@NewID)AS NextID -- Replace NewId

 

Let me know if you have question on my query.

Can replace line 4 by since you alreay have lastid

SELECT @NewID =(LTRIM(SUBSTRING(@LastID,2,60)+1)) FROM test

all the best

 

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Thanks to everyone who replied. I really appreciate it :)

Hey guys, i alread started a thread for sql beginers/Pros to discuss about problem and tips. pls can you guys come to that thread with tips and problem.

http://www.sajha.com/sajha/html/OpenThread.cfm?forum=144&ThreadID=59687#599585

Hi,
I am still having issues with this problem. I already have a live table A. This table has a column CustomerNumber. I am inserting new values in this table, but for column CustomerNumber, the initial value (starting from where I insert without losing the previous values already in this live tableA) needs to start from R000001. The dayatype of this Column is already predefined to varchar(60) and I cannot change that. I am totally lost:(

Thanks in advance for any help.

This is what I did:

create function NextCustomerNumber()
returns char(7)
as
begin
    declare @lastval char(7)
    set @lastval = (select max(CustomerNumber) from absences)
    if @lastval is null set @lastval = 'C000001'
    declare @i int
    set @i = right(@lastval,4) + 1
    return 'C' + right('000000' + convert(varchar(10),@i),4)
end

THEN,
insert into TableA(CustomerNumber)
SELECT NextCustomerNumber()  as CustomerNumber

BUT this gives me error

Incorrect syntax near the keyword 'SELECT'.

'NextCustomerNumber' is not a recognized function name.

insert into TableA(CustomerNumber)
SELECT dbo. NextCustomerNumber()  as CustomerNumber

i guess suman got da right point.  if still dont work let us know...

Hey guys,
Thanks for the prompt help. Now I was able to insert values, but they were all the same(C000001) for the columns that were inserted since my query does multiple inserts satisfying a condition. But I want values to be inserted as C000001, C000002, and so on. So what I did, was create a Table B, with

CustomerID varchar(60) ---same datatype as TableA
dbID int identity not null primary key,

Then insert into TableB(CustomerID)
select CustomerID........

Now I am trying to insert these values from TableB to TableA,
and i get this error

Msg 512, Level 16, State 1, Procedure NextCustomerNumber, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any ideas?

Also,
I am getting all the information from:

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

as arnzombie suggested.

Ok,
It works now!! YAY

alter function NextCustomerNumber (@id int)
returns char(5)
as
begin
return 'C' + right('0000' + convert(varchar(10), @id), 4)
end

Then,
select
...
dbo.NextCustomerNumber(dbID) as CustomerID,
...

Thanks to all of you!! YOU GUYS ROCK

One minor issue is that it starts from

C0534 instead of C0000..hmmm

Sorry guys,
I have another problem

I have to do insertion once a week.

When I try to insert (multiple data) more than once, the CustomerId gets repeated.
Example, the first time I inserted it, the values were C0534.....C0573
Now again, when I insert another block of data, the same set of values for CustomerId get repeated.

Any help is greatly appreciated