How to generate a series

[asimasm]

I want to generate a sequence like 1,2,3,4...1000 using a single select statement. Can anybody tell me thanks

 

[tlbroadbent]

Do you want to store the series in a table or an array? Do you want to return a recordset containing the numbers 1 to 1000 to client software? Or do you simply want to display the series on screen or print it? Why did you specify generating a series in a SELECT statement? Do you really want to know how to use T-SQL to generate the series, regardless of the statements required.

Your question is very vague. Please elaborate. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[asimasm]

I need the series to insert some data in a table which contains a primary key. I am trying to insert data into a table by using a select statement. i.e

Insert into TblTarget select Col_PrimaryKey, col_1,col_2 from TblSource;

Now in this case col_1, col_2 are imported from source table but col_PrimaryKey is to be supplied by me. I dont want to use auto number as primary Key column. so that's why i need a sequence so that i can subsitude col_primarykey with my sequence. If u know any other solution then plz let me know.

 

[tlbroadbent]

Here is one way to do what you want. It takes advantage of the power of the IDENTITY column without requiring one on your permanent table. You'll need to modify the CREATE TABLE statement for your data types.[ol]Create Table #t (Col_PrimaryKey int identity (1,1), col_1 varchar(40), col_2 varchar(40))

Insert Into #t Select Col_PrimaryKey, col_1, col_2 From TblSource

Insert TblTarget Select * From #t

Drop table #t[/ol] Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tlbroadbent]

I forgot to add SET ROWCOUNT 1000 at the beginning of the script to limit the generation of records to 1000 if that is required. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin