Generate ID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Generate ID

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2013-03-27 : 07:29:33

Do you know how to create Store Procedure where in this Store Procedure, we must generate ID like this "INV130155P095" and insert to table A?
INV + 130155(juliandate) + P095 (random character)

please help for the script

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 08:10:32

Try this..........
DECLARE @D DATETIME = GETDATE()
SELECT 'INV'+ CAST((DATEPART(year,@D)-1900)*1000+DATEDIFF(day,@D,CAST('01-01-'+CAST(DATEPART(year,@D) AS CHAR(4)) AS SMALLDATETIME))+1 AS VARCHAR) + LEFT(NewId(), 4)

kabon
Starting Member

48 Posts

Posted - 2013-03-27 : 23:12:46

juliandate get from this:
select right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) from F_DATES WHERE RECID='ID0010001'

and how to insert that Store Procedure into table A in [Column 0]?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 00:13:54

--May be this?
Insert into TableA([Column 0])
SELECT 'INV' + right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) + LEFT(NewId(), 4)
FROM F_DATES
WHERE RECID='ID0010001'

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 03:06:33

my bos want me to use ROW_NUMBER for replace random character, do you know how to use it?

bandi, do you have email? i want to ask you about programming language SQL

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 03:44:38

Any one can help you regarding SQL in the forum...

Let us know the full description about your requirement....
Post some sample input data and also output based on your ligoc....

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 03:51:03

I have table A that have 10000 records, and [Column 0] is blank.
[Column 0] must be input by file that format like I said before that must be sequence using ROW_NUMBER and can fill the blank at [Column 0] as the number of records.

can you help me?

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 03:53:40

I must create the store procedure Mr.Bandi

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 04:23:56

why you need ROW_NUMBER here?
Post F_DATES table's data and structure....

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 04:32:38

because it used to get sequence number that can't be duplicated.

table F_DATES is use to get date in that table where the format to get is right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5)

This case is how to insert [Column 0] in table A with data that format is INV+right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5)+ROW_NUMBER order by total records that must be sequence.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 04:41:11

--May be this?
Insert into TableA([Column 0])
SELECT 'INV' + right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) + 'P' + RIGHT('000' + CAST(ROW_NUMBER() OVER(ORDER BY RECID) AS VARCHAR) , 3) FROM F_DATES

--Illustration (execute this sample script once)
DECLARE @Sample TABLE(id INT)
INsert into @Sample VALUES( 10), (20), (30), (40), (100), (130),(200)
SELECT 'INV' + 'YourJulianDate' +'P'+ RIGHT( '000' + CAST(ROW_NUMBER() OVER(ORDER BY id) AS VARCHAR), 3) from @Sample

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 04:50:01

why you use this values ( 10), (20), (30), (40), (100), (130),(200)?
random character like P095 isn't use again, now use ROW_NUMBER that have 5 character and must be sequence.

my yuliandate is take from:
select XMLRECORD.value('(/row/c14)[1]','varchar(10)') from t24testsby.dbo.F_DATES WHERE RECID='ID0010001'

and how to read that i can read how number records to fill this format?
I want to read number records from [column 1] in table A

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 04:56:36

i have try your query, i have new query that it is true, but row_number that result can be increase as number of records.

Insert into A(ID)
SELECT 'INV' + right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) + RIGHT('00000' + CAST(ROW_NUMBER() OVER(ORDER BY RECID) AS VARCHAR) , 5) FROM t24testsby.dbo.F_DATES WHERE RECID='ID0010001'

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 05:02:41

here RECID should be your Primary Key column name
(ROW_NUMBER() OVER(ORDER BY RECID)

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 05:19:47

wow how great you are, thanks a lot.

in (ROW_NUMBER() OVER(ORDER BY [Column 1] asc), do you know how the different using asc or not?

why you use cast not convert?

kabon
Starting Member

48 Posts

Posted - 2013-03-28 : 05:31:10

Bandi, it still duplicate.

select right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) AS jul into ##jul from t24testsby.dbo.F_DATES WHERE RECID='ID0010001'

Insert into A(ID)
select 'INV' + (select * from ##jul) + RIGHT('00000' + CAST(ROW_NUMBER() OVER(ORDER BY [Column 1] asc) AS VARCHAR) , 5) from ctmuratx_20130327

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 05:31:46

You can use either of one( CAST, CONVERT)....

ROW_NUMBER() OVER(ORDER BY [Column 1] asc ) ----> By default it will consider Ascending...
So if you want row number by descending, you must specify DESC keyword instead of asc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-28 : 06:59:32

[code]CREATE TABLE dbo.MyOwnSequenceTable
(
DocType CHAR(3) NOT NULL,
DocDate INT NOT NULL,
SubType CHAR(1) NOT NULL,
Sequence SMALLINT NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX UCX_MyOwnSequenceTable ON dbo.MyOwnSequenceTable (DocType, DocDate, SubType);
GO
CREATE PROCEDURE dbo.uspGetMySequenceNumber
(
@DocType CHAR(3),
@DocDate DATE,
@SubType CHAR(1),
@Number CHAR(13) OUT
)
AS

SET NOCOUNT ON;

DECLARE @Result TABLE
(
DocType CHAR(3) NOT NULL,
DocDate INT NOT NULL,
SubType CHAR(1) NOT NULL,
Sequence SMALLINT NOT NULL
);

MERGE dbo.MyOwnSequenceTable AS tgt
USING (
VALUES (@DocType, 10000 * (DATEPART(YEAR, @DocDate) % 100) + DATEPART(DAYOFYEAR, @DocDate), @SubType, 1)
) AS src(DocType, DocDate, SubType, Delta) ON src.DocType = tgt.DocType
AND src.DocDate = tgt.DocDate
AND src.SubType = tgt.SubType
WHEN MATCHED
THEN UPDATE
SET tgt.Sequence += src.Delta
WHEN NOT MATCHED BY TARGET
THEN INSERT (
DocType,
DocDate,
SubType,
Sequence
)
VALUES (
src.DocType,
src.DocDate,
src.SubType,
src.Delta
)
OUTPUT inserted.DocType,
inserted.DocDate,
inserted.SubType,
inserted.Sequence
INTO @Result
(
DocType,
DocDate,
SubType,
Sequence
);

SELECT @Number = DocType + CAST(DocDate AS VARCHAR(6)) + SubType + RIGHT('00' + CAST(Sequence AS VARCHAR(3)), 3)
FROM @Result;
GO

DECLARE @Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'INV', '20130328', 'P', @Number OUT
SELECT @Number
GO 3

DECLARE @Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'INV', '20130328', 'Q', @Number OUT
SELECT @Number
GO 3

DECLARE @Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'INV', '20130328', 'P', @Number OUT
SELECT @Number
GO 3

DECLARE @Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'PSD', '20130328', 'A', @Number OUT
SELECT @Number
GO 3[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources