T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?

Sep 3, 2014

I'm trying to do a simple insert into a table, something like this:

insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)

So, the SELECT would generate something that looks like this:

ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)

Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:

ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)

What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.

Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.

What I'm hoping to do is rewrite my SQL like this:

insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'

Is there an easy way to do this?

View 9 Replies


ADVERTISEMENT

Generate Sequence Number In Sql???

Mar 15, 2005

Hi,
I have written the following StoredProcedure





Code:

create Procedure spCreateQuestion(
@QuestionName varchar(30)
)

as

declare @newAnswerId int
declare @newQuestionId int

set @QuestionName='New Question'

BEGIN TRANSACTION Q1

--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions

--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName

BEGIN TRANSACTION QA1

--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers

--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this

--update Questions Table with this new Answerid
UPDATE Questions
set
AnswerId=@newAnswerId
where QuestionId=@newQuestionId

COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1



I think the second Transaction is not locking the table.so some how i should be
able to get the newly create AnswerId

i can't use the identity column in my tables

Can some one please have a look at it and suggest me how do we go about it..

View 8 Replies View Related

Generate Sequence Number Basing On Each Id

Mar 28, 2008

Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC


Expected results as below :
------------------

Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2

Thanks in advance

View 4 Replies View Related

How To Generate Sequence No?

Oct 29, 2007

Dear all,

I'm new in writing t-sql. Now I want to perform the following task:

1) pass a non zero integer to sql procedure, the procedure will select fields from tableA, for example,

if input parameter is 5, the result is (the column seq no is not in tableA, I want to generate sequence no):

seq no Customer code Customer name
------------------------------------------------------------------
6 abcde Peter
7 efghi John
8 jklmn Mary
:
etc


How to do this?

View 5 Replies View Related

Generate Sequence No In One Column

Mar 20, 2007

helen writes "I have a sql table:
NAME AGE
Anna 10
Susan 5
Dina 12

Please help me to come up with a QUERY STATEMENT with this result:
SEQNO NAME AGE
1 Anna 10
2 Susan 5
3 Dina 12 "

View 2 Replies View Related

Methods To Generate ER Diagrams Programmatically

Sep 4, 2007

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.
Any help would be highly appreciated.

Thanks
-Akash

View 1 Replies View Related

Generate 8 Character Alphanumeric Sequence

Apr 20, 2015

Requirements
•ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
•Generate from Alphabets – A through Z Numbers -0 to 9
•Generate Unique Sequence (No Duplicates).
•Must Eliminate letters I and O
Output Expected
•AA00AA00………..ZZ99ZZ99
•Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)
AS
BEGIN
DECLARE @OUT AS CHAR(8)--,@Id as Bigint

[Code] ....

View 1 Replies View Related

Programmatically Generate Rdl Files With Image File

Oct 1, 2007

Hello,

I would like programmatically generate rdl files with image.

I am using this XML Text Writer to create the rdl file.

But i have no idea how can i carryon on this task.

Can anyone help me in this topic?

Thanks.

View 4 Replies View Related

Generate Thousands Of SSRS PDFs Programmatically With SP Or SSIS

Jul 18, 2007

I have a report that I'd like to involve in delivering tons of PDFs for each of our customers for a billing cycle. There is a table that the report reads from that gives it all its data necessary for the report (which is a customer bill) and the table also has a column that has the file name for the PDF for that particular customers bill for the report. Basically each table row represents one output report (bill) and each table row has its name nicely formated for me. Writing the report is not a problem. Figuring out how to run through thousands of rows generating a PDF for each with the file name from the table is my challenge.

I would like to create either a SP or a SSIS package (that is scheduled) that can run a report for each line of data in my table and spit out a PDF file to a UNC path. It might have to generate thousands of PDFs to a UNC path that has plenty of space. There will be another SSIS package that moves the PDFs later to their proper directory.

So, is there someone who has done this before? Any suggestions? Is there a quick path to doing this, would it take a ton of time? Any tutorials out there?

Thanks,
Keith

p.s. My preference is to do this in SP's or SSIS and if I need a .NET language to do that in VB.NET, but I'd rather avoid that if I can.

View 3 Replies View Related

SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence

Apr 20, 2015

GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES

Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O

Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)

[Code] .....

View 9 Replies View Related

Log Sequence Number

Apr 16, 2002

Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ?
10x,
Clara

View 1 Replies View Related

Sequence Number

Dec 29, 1998

In SQL 6.5 object dependencies window, what does the sequence number means?

Thanks,

Sam

View 1 Replies View Related

Sequence Number Help

Nov 17, 2005

I have the following stored procedure:

CREATE PROCEDURE dbo.ABR_HDR_INSERT

@id int output,
@status int,
@mode int,
@sessid varchar(100)

AS

declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int

SELECT @ddo = a.DDO_DSCR_SHORT
FROM dbo.DIM_DDO a
JOIN dbo.Temp_ABR_HDR b
ON a.DDO_ID = b.DDO
WHERE b.SESSIONID = @sessid

SELECT @ay = AY
FROM dbo.Temp_ABR_HDR
WHERE SESSIONID = @sessid


-- set the default seq_no
SELECT @seq_no = 1
-- get the max abrid. if no record return the seq_no will be 1
SELECT @seq_no = convert(integer, max(right(abrid, 4)))
FROM dbo.ABR_HDR
WHERE left(abrid, 7) = @ay + @ddo

-- convert @seq_no to string prefix by 0
SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)


Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

SELECT

@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status

FROM dbo.Temp_ABR_HDR


SELECT @id = @@identity

return @id
GO

ABRID gets inserted as a <NULL> value. I can't figure out why? If I comment out the following then ABRID will insert without the sequence number:

CREATE PROCEDURE dbo.ABR_HDR_INSERT

@id int output,
@status int,
@mode int,
@sessid varchar(100)

AS

declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int

SELECT @ddo = a.DDO_DSCR_SHORT
FROM dbo.DIM_DDO a
JOIN dbo.Temp_ABR_HDR b
ON a.DDO_ID = b.DDO
WHERE b.SESSIONID = @sessid

SELECT @ay = AY
FROM dbo.Temp_ABR_HDR
WHERE SESSIONID = @sessid


-- set the default seq_no
--SELECT @seq_no = 1
-- get the max abrid. if no record return the seq_no will be 1
--SELECT @seq_no = convert(integer, max(right(abrid, 4)))
--FROM dbo.ABR_HDR
--WHERE left(abrid, 7) = @ay + @ddo

-- convert @seq_no to string prefix by 0
--SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4),@seq_no)), 4)

SELECT @abrid = @ay + UPPER(@ddo)

Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

SELECT

@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status

FROM dbo.Temp_ABR_HDR


SELECT @id = @@identity

return @id
GO

So, the code that sets the sequence number is what is causing the <NULL> value.

Any help is appreciated.
Thanks,
-D-

View 4 Replies View Related

Sequence Number

Oct 3, 2007

suppose I have the following table grouped by memid

memiddx
3455
3322
3232
433
43434

I want to attach sequence number for each unique value of dx per memid as
below

memiddxSEQ
34551
33222
32323
4331
434342

I am using a cursor right now and it takes a lot of time if my table is large.
Is there a more efficient way of doing this.

Thanks much.

View 3 Replies View Related

Number Sequence

Jan 11, 2008

is it possible to generate a number sequence in a query (without using loop). I want the output to look as
-------
ID
-------
1
2
3
4
5
6
7
8
9
....
upto the last number I give in the query

View 13 Replies View Related

Row Sequence Number

Apr 4, 2006

Hello all,I´m currently using a SQL Serve 2K. Would like to do a selectwhich returns the row number - this should not be physically stored inthe database. So for example, I would like to do a query against theCUSTOMER table and receive:* rowID || name1 Evander2 Ron3 Scoth4 JaneI don´t want to store the ID, because if I change the order byclause, the sequence may modifiy, and, for another example, having thesame set of data, I would receive:* rowID || name1 Scoth2 Ron3 Jane4 Evander could someone help me ?best regards,Evandro

View 3 Replies View Related

T-SQL (SS2K8) :: Repopulating A Sequenced Column That Has Gone Out Of Sequence

Jul 5, 2014

We have an application which has had a big design floor and is now causing an issue which needs to be rectified. It inserted data in batches of 2000 rows which should have always been in sequence. A column was used which recorded an ever increasing numerical value for each row that was inserted. So batch 1 would have a start value of 1 and an end value of 2001. Batch 2 would have a start value of 2002 and an end value of 4002 etc.

The problem is the application which inserted these records in order, missed some records. So in actual fact we have:

Batch 1 = seq 1 - 2001 (total 2000)
Batch 2 = seq 2002 - 3998 (total 1996, 4 missing)
batch 3 = seq 3999 - 5999 (total 2000)

Now the missing data has been found, it will be inserted at the end of the table, as we cannot insert it where its should be as the sequence value has been used by the next batch already. Lets say the table is 10,000 rows so far, so the seq for batch 2, for example, will be 2002-3998, then after the missing data is inserted, 10,001 - 10,005. The application uses these sequences to show the range of data so where it should be 2000 rows being returned, it would now show 8003 rows - all the other batches between the first and last sequences for the batch!

I need to change the sequence values across the whole table so they are in order. I can select the data in the correct sort order as there is another unique key on the table, but unsure how best to use this to change the sequence column to be in order for all rows. Adding an identity column would work and then remove and rename the seq column, but I obviously cannot add this based on another columns sort order. The identity column would use the clustered key which is in the incorrect order!!

View 9 Replies View Related

Generating Sequence Number

Aug 29, 2001

Hello,

I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.

Thank you so much.

NK

View 3 Replies View Related

Sequence Number Generation

Feb 3, 2005

Does anyone know an efficient method for generating a sequence number in the following form?

Starting with 2 columns
1 2
----
A X
A Y
B X
B Y
B Z
C X

I want to then generate a third column as follows:
1 2 3
-------
A X 1
A Y 2
B X 1
B Y 2
B Z 3
C X 1



The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.

Any ideas? Thanks.

View 10 Replies View Related

Generating Sequence Number....

Oct 3, 2007

Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.


Col1
----
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........

Col1 Col2
---- -----
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1


Thanks In Advance,
Rahul Jha

View 14 Replies View Related

Generating Sequence Number....

Nov 14, 2007

Hello folks,
I have a table where the records are like followings.

ID Value
---------------
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa



I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.

ID Value SeqId
-----------------------------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1




Thanks,
Rahul Jha

View 14 Replies View Related

Add Sequence Number Within Group?

Oct 13, 2013

I have the following data:

POL# POL_EFF_DATE
123 1-1-2012
123 1-1-2012
123 1-1-2012
123 1-1-2013
123 1-1-2013
456 1-1-2012
456 1-1-2012
456 1-1-2013
456 1-1-2013

I want to add sequence number to each group with same Pol# and Effective Date like this:

POL# POL_EFF_DATE Seq
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2013 2
123 1-1-2013 2
456 1-1-2012 1
456 1-1-2012 1
456 1-1-2013 2
456 1-1-2013 2

is there a set based function to accomplish this in SQL 2008?

View 2 Replies View Related

Getting Sequence Number In 2000

Apr 18, 2008

I have a table like this

create table
#invoice (invoice_no varchar(5), invoice_amount int)

INSERT INTO #invoice
SELECT 'A', 100 UNION
SELECT 'A', 200 UNION
SELECT 'A', 300 UNION
SELECT 'B', 400 UNION
SELECT 'B', 500 UNION
SELECT 'B', 600 UNION
SELECT 'B', 700


Now I want a output like this

INVOICE_NO AMOUNT SEQUENCE_NO
A 100 1
A 200 2
A 300 3
B 400 1
B 500 2
B 600 3
B 700 4

The sequence_no should set back to 1 on change of invoice_no
these are items in a an invoice. they just want a sequence_no

I can do this using cursors, but if anyone can help with WHILE LOOP
would be great

THANKS






-----------------------------------------------------------------------------------------------
Ashley Rhodes

View 10 Replies View Related

Getting Sequence Number Generations

May 31, 2006

I am newbie to SQL Server 2005.

Can any experts please give me code for how to generate automatic sequence numbers in sql server 2005?

I want to use this generated sequence number as a primary key to update the records.

Thanks in advance,
Vani

View 2 Replies View Related

Sequence Number Generation

Jan 25, 2008

Hi,
I have an error table that is to be updated by more than one package.
There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table.
When more than one package runs parallely, conflict occurs in generating the sequence number.
How can this be handled?

View 1 Replies View Related

Unique Number/sequence

Jul 23, 2005

Hallo,Hot to get unique, sequential number during executionof stored procedure ?I can create table with autoincrement column,add record, get ident_current and delete recordeach time i need the number.However its not elegant i guess.best regardspluton

View 3 Replies View Related

Auto Number Sequence

Oct 6, 2006

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)



Whats the easiest way to do this?

View 7 Replies View Related

Mirroring :: Log Sequence Number

Jun 29, 2015

Why log sequence number gets broken in SQL server?

View 4 Replies View Related

Sequence Number For Records

Nov 15, 2007

Hi...

I have Sql statement more like this

SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a


rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
1 111 Smith TECH
2 222 John TECH
3 333 Lenny TECH



How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :

rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
3 111 Smith TECH
4 222 John TECH
5 333 Lenny TECH

View 4 Replies View Related

SQL Server Sequence Number

Mar 27, 2008

Hi,

Does any one have information about how 'large' the SQL Server Sequence Number generator can go,
like 1 to 1 billion, or to 12 zeros, etc?

Thanks.

--John

View 1 Replies View Related

Add Sequence Number To Data

Sep 17, 2007



Hi all, I need some your help.
I want to add sequence number to data flow below:

Cust_Name | Month
=======================
Chonnathan | 5
Chonnathan | 4
Chonnathan | 7
Derec | 3
Derec | 9

and the result i need is:

SEQ_nbr | Cust_Name | Month
================================
1 | Chonnathan | 4
2 | Chonnathan | 5
3 | Chonnathan | 7
1 | Derec | 3
2 | Derec | 9

How can I do it in Integration Service to show like the above?

Thank you for your respones,
Chonnathan

View 1 Replies View Related

Chalenge Returning The First Available Number In A Sequence

Aug 8, 2007

 Hi all,Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.Given the client table below my query would return 4 because its the lowest number availeble. Thanks. client tableclnum12367 

View 4 Replies View Related

Creating Alphanumberic Sequence Number

Oct 12, 2007

Database in:SqlServer builtin interface with VS05
Using:ASP.NET VISUAL STUDIO 2005
I want to create a unique ID that identifies a row.
Id:STU+(SeqNumber) for Student and
Id:EMP+(SeqNumber) for Employee
[Question]:But how can i create sequence number and store it for use like that of ORACLE

Current Idea:Based on registration info if type is student generate STU+seq num and insert to table along with username and other details and if employee like wise.
So a procedure will receive this from user and append that with seqnumber and stores that in the table

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved