declare @SQL varchar(max) set @SQL=' SELECT' declare @Select varchar(max) set @Select='' declare @INSERT varchar(max) set @INSERT='' declare @n int set @n=10 select @SQL=@SQL+C,@INSERT=@INSERT+I,@Select=@Select+S from ( select case when number<>@n+1 Then ' CAST(NULL as bigint) as ['+CAST(number as varchar)+'],' else ' CAST(1 as bigint) as ['+CAST(number as varchar)+'],' end as C, 'isnull(CAST(['+CAST(number as varchar)+'] as varchar),'''' ),' as S, case when (@n+1-number)>0 Then ' INSERT INTO #t( '+ (select '['+CAST(a.number as varchar(100))+']'+',' from master..spt_values a where type='P' and a.number BETWEEN @n+2-t.number and @n+t.number for xml path(''))+'['+CAST(@n+1-number as varchar)+'],['+CAST(@n+1+number as varchar)+'],ID) select '+ (select '['+CAST(a.number-1 as varchar)+']+['+CAST(a.number+1 as varchar)+'],' from master..spt_values a where type='P' and a.number BETWEEN @n+2-t.number and @n+t.number for xml path(''))+'1,1,'+CAST(number+1 as varchar)+' from #t where ID=(select max(ID) from #t) ' else '' end as I from master..spt_values t where type='P' and number BETWEEN 1 and @n*2+1 )a set @SQL=@SQL+' 1 as ID INTO #t '+@INSERT+' select '+@Select+' ID from #t order by ID asc' exec(@SQL)