I need to use a select expression in a while loop and I use the below sample code:
declare @i integer
set @i=1
while (@i<10)
begin
select @i as m;
set @i=@i+1
END
this code returns 10 separate table! I want it to return all select results in one table... is that possible? if yes... how?
You can use a temp table or table variable for this.
Here's how to do it using a temp table.
CREATE TABLE #t (m INT)
DECLARE @i INT
SET @i=1
WHILE (@i<10)
BEGIN
INSERT INTO #t SELECT @i
SET @i=@i+1
END
SELECT m FROM #t
Very similar with a table variable
DECLARE @t TABLE (m INT)
DECLARE @i INT
SET @i=1
WHILE (@i<10)
BEGIN
INSERT INTO @t SELECT @i
SET @i=@i+1
END
SELECT m FROM @t
It is not possible. Each SELECT statement generates its own result set. You can use temp table to add results of each iteration and then get all in one table. To generate sequence of integers you can use this (for SQL SERVER 2005 + )
;WITH CTE
AS
(
SELECT 1 N
UNION ALL
SELECT N + 1 FROM CTE
WHERE N<10
)
SELECT N FROM CTE
squillman got it...with #t (create table # - table persisted at top-level scope while session is open - if you have several batch statements, you can reference this table in any after declaration until you drop the table)
Cris also got it with @test (declare @ table - variable - only persisted in the current scope - single execution batch block...note that there are several performance issues that can be introduced if you use this)
the last type of temp table you can use is a global temp table (create table ## - lasts as long as the session that created it stays open or until it is dropped)
with #t, you may want to add this to the beginning of your script if you don't close the session:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
Enjoy the temp tables!
declare @i integer
DECLARE @test TABLE(
m /*your data type*/
)
set @i=1
while (@i<10)
begin
insert into @test select @i;
set @i=@i+1
END
select * from @test
