Can we generate sequence numbers (ie: 1 to 10)

Posted by Sarvesh under Sql Server on 12/23/2009 | Views : 5771 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi sql server experts

Can we generate sequence records (ie: 1 to 10).

But, TABLE / UNION ALL / LOOP Should not be used.

it's urgent please.....

thanks in advance
Sarvesh S

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Vuyiswamb on: 12/23/2009 [Member] [MVP] [Administrator] NotApplicable

0	This is how is done create table TestTable ( ID int identity(1,1) not null, Fname varchar(30) null, Lastname varchar(30) null, Age INT NULL ) DECLARE @FIRST INT DECLARE @LAST INT SET @FIRST = 1 SET @LAST = 10 WHILE @FIRST <= @LAST BEGIN SET @FIRST = @FIRST + 1 INSERT INTO TestTable VALUES('VUYISWA','MASEKO',28) END Thank you for posting at Dotnetfunda Thank you for posting at Dotnetfunda [Administrator] Sarvesh, if this helps please login to Mark As Answer. \| Alert Moderator

This is how is done


create table TestTable 

(

ID int identity(1,1) not null,

Fname varchar(30) null,

Lastname varchar(30) null,

Age INT NULL

)


DECLARE @FIRST INT

DECLARE @LAST INT

SET @FIRST = 1

SET @LAST  = 10 

WHILE @FIRST <=  @LAST

BEGIN 

SET @FIRST = @FIRST + 1 

INSERT INTO TestTable

VALUES('VUYISWA','MASEKO',28)

END

Thank you for posting at Dotnetfunda

Thank you for posting at Dotnetfunda
[Administrator]

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 12/23/2009 [Member] [MVP] Silver

0	Hi Thanks for the intresting question posted.... You can use Recursion CTE (But, Recursion CTE will also have UNION ALL) ;WITH CTEs AS ( SELECT 1 AS Sequence UNION ALL SELECT Sequence + 1 FROM CTEs WHERE Sequence <10 ) SELECT * FROM CTEs Result Sequence 1 2 3 4 5 6 7 8 9 10 Limitation: But, The maximum recursion 100 has been exhausted before statement completion. Cheers Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Sarvesh, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Thanks for the intresting question posted....

You can use Recursion CTE (But, Recursion CTE will also have UNION ALL)

;WITH CTEs
AS
(
SELECT 1 AS Sequence
UNION ALL
SELECT Sequence + 1 FROM CTEs WHERE Sequence <10
)
SELECT * FROM CTEs

Result

Sequence
1
2
3
4
5
6
7
8
9
10

Limitation:
But, The maximum recursion 100 has been exhausted before statement completion.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts