[VIEWED 9794
TIMES]
|
SAVE! for ease of future access.
|
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-04-08 7:10
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
|
gurkha's
Please log in to subscribe to gurkha's's postings.
Posted on 06-04-08 9:12
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.............
|
|
|
techGuy
Please log in to subscribe to techGuy's postings.
Posted on 06-04-08 9:27
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
STUPIDA
Please log in to subscribe to STUPIDA's postings.
Posted on 06-04-08 9:29
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
How about creating output with identity value and then concatenate and insert to the column.
thanks
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-04-08 10:59
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Thanks to who answered. But I am still confused. Can you please show me a dummy function?
|
|
|
sumansuman
Please log in to subscribe to sumansuman's postings.
Posted on 06-05-08 8:05
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Ask SQL Guru RawBee
Suman Anwar Suman
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-05-08 9:54
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-05-08 10:00
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Can replace line 4 by since you alreay have lastid
SELECT @NewID =(LTRIM(SUBSTRING(@LastID,2,60)+1)) FROM test
all the best
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-05-08 10:05
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-05-08 11:07
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Thanks to everyone who replied. I really appreciate it :)
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-05-08 11:16
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 1:04
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 1:13
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
sumansuman
Please log in to subscribe to sumansuman's postings.
Posted on 06-06-08 2:08
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
insert into TableA(CustomerNumber) SELECT dbo. NextCustomerNumber() as CustomerNumber
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-06-08 2:46
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
i guess suman got da right point. if still dont work let us know...
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:01
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Hey guys, ![](../chat/smileys/m-smile.gif) 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?
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:07
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Also, I am getting all the information from: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server as arnzombie suggested.
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:17
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Ok, It works now!! YAY ![](../chat/smileys/m-biggrin.gif) 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
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:22
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
One minor issue is that it starts from C0534 instead of C0000..hmmm
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:48
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Sorry guys, I have another problem ![](../chat/smileys/m-sad.gif) 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
|
|