I want to get the result like this
Here is my code
declare @current int
declare @Temp Table(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int)
select @current = 1
while @current <= 10
begin
--I want to insert here
select @current = @current + 1
end
select * from @Temp
How can I insert? Thanks for any help.
insert into @temp(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10)
select 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
There is no reason to use a while loop. In general, you should be thinking in a set-based way, rather than an iterative way, when using SQL.
No need of while loop to do this
Insert into @temp(c1,c2,c3,.c10)
select @current-1,@current,@current+1,..@current+9
I would avoid WHILE in this case using INSERT ... SELECT with CROSS JOIN:
declare @Temp Table(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int)
insert into dbo.TargetTable (...columns...)
select t.*, n.Num
from @Temp t
cross join (
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) n(Num)
or (within sqlcmd / SSMS) using GO (this keyword isn't T-SQL keyword/statement):
insert into dbo.TargetTable (...columns...)
values (...)
go 10
go 10 executes current SQL Server batch ten times.
