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

Posted by Sarvesh under Sql Server on 12/23/2009 | Views : 4413 | Status : [Member] | Replies : 2
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response