SELECT TOP 1000 row_number() OVER(ORDER BY name) AS ROW INTO NewTable FROM master.dbo.sysobjects /*SELECT * FROM NewTable ROW 1 2 3 4 (1000 行受影响) */
if OBJECT_ID('table2008') is not null drop table table2008 go select top 1000 id=identity(int,1,1) into table2008 from syscolumns a,syscolumns b,syscolumns c,syscolumns d
from syscolumns a,syscolumns b,syscolumns c,syscolumns d---- 不用那么多也可以
-- 2000 select top 1000 identity(int,1,1) as row into #temp from sysobjects a,sysobjects b-- 2005 SELECT TOP 1000 row_number() OVER(ORDER BY name) AS ROW INTO #temp FROM sysobjects a,sysobjects b--或者 log2 次方 循环
DECLARE @COUNT INT = 1,@SQL NVARCHAR(max),@PARAMS VARCHAR(100),@A VARCHAR(100) SELECT @A = CHAR(13) SELECT @SQL = '' SELECT @PARAMS = ' UNION ALL ' WHILE(@COUNT<=1000) BEGIN SELECT @SQL = @SQL+ @A+'SELECT '+CAST(@COUNT AS VARCHAR)+@A+'UNION ALL' SELECT @COUNT = @COUNT + 1 ENDSELECT @SQL = SUBSTRING(@SQL,0,LEN(@SQL)-8)SELECT @SQL = ' SELECT T.* INTO NEWTABLE FROM ('+@SQL+') T'EXEC(@SQL)
其他人都对了。 学习了。 这个贴只能平分得分了。哈哈。 select 1 union select 2 union 。 select 1000 这种写法就不得分了啊。哈哈 不知道还有没有别的方法。
select number from master..spt_values where type='p' and number between 1 and 1000
;WITH cte_1 AS(SELECT col=1 UNION ALL SELECT 1), cte_2 AS(SELECT a.col FROM cte_1 a, cte_1 b), cte_3 AS(SELECT a.col FROM cte_2 a, cte_2 b), cte_4 AS(SELECT a.col FROM cte_3 a, cte_3 b), cte_5 AS(SELECT a.col FROM cte_4 a, cte_4 b), cte_6 AS(SELECT a.col FROM cte_5 a, cte_5 b) SELECT TOP(100000) rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM cte_6 a, cte_6 b --TOP后面的N就看你了
;with cte as (select 1 num union all select num+1 from cte where num<1000 ) select num from cte OPTION(MAXRECURSION 1000) 2005以上就这样吧。
select top 100000 row_number() over(order by typy) as num into #temp from master..spt_values a join master..spt_values b on 1=1
select top 100000 row_number() over(order by type) as num into #temp from master..spt_values a join master..spt_values b on 1=1
不好意思 我没MSSQL的环境 是什么顺序有问题啊
数字顺序是乱的在我这 ms2008 express版。 我需要select * from #temp order by num
select number from master..spt_values where type='p' and number>0 and number<=1000 /* number ----------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ......(1000 行受影响)*/
/*SELECT * FROM NewTable
ROW
1
2
3
4
(1000 行受影响)
*/
if OBJECT_ID('table2008') is not null drop table table2008
go
select top 1000 id=identity(int,1,1) into table2008
from syscolumns a,syscolumns b,syscolumns c,syscolumns d
不用那么多也可以
-- 2000
select top 1000 identity(int,1,1) as row into #temp
from sysobjects a,sysobjects b-- 2005
SELECT TOP 1000 row_number() OVER(ORDER BY name) AS ROW INTO #temp
FROM sysobjects a,sysobjects b--或者 log2 次方 循环
DECLARE @COUNT INT = 1,@SQL NVARCHAR(max),@PARAMS VARCHAR(100),@A VARCHAR(100)
SELECT @A = CHAR(13)
SELECT @SQL = ''
SELECT @PARAMS = ' UNION ALL '
WHILE(@COUNT<=1000)
BEGIN
SELECT @SQL = @SQL+ @A+'SELECT '+CAST(@COUNT AS VARCHAR)+@A+'UNION ALL'
SELECT @COUNT = @COUNT + 1
ENDSELECT @SQL = SUBSTRING(@SQL,0,LEN(@SQL)-8)SELECT @SQL = ' SELECT T.* INTO NEWTABLE FROM ('+@SQL+') T'EXEC(@SQL)
学习了。
这个贴只能平分得分了。哈哈。
select 1
union
select 2
union
。
select 1000 这种写法就不得分了啊。哈哈
不知道还有没有别的方法。
cte_2 AS(SELECT a.col FROM cte_1 a, cte_1 b),
cte_3 AS(SELECT a.col FROM cte_2 a, cte_2 b),
cte_4 AS(SELECT a.col FROM cte_3 a, cte_3 b),
cte_5 AS(SELECT a.col FROM cte_4 a, cte_4 b),
cte_6 AS(SELECT a.col FROM cte_5 a, cte_5 b)
SELECT TOP(100000) rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM cte_6 a, cte_6 b
--TOP后面的N就看你了
;with cte as
(select 1 num
union all
select num+1 from cte where num<1000 )
select num from cte OPTION(MAXRECURSION 1000)
2005以上就这样吧。
select top 100000 row_number() over(order by typy) as num into #temp
from master..spt_values a join master..spt_values b on 1=1
select top 100000 row_number() over(order by type) as num into #temp
from master..spt_values a join master..spt_values b on 1=1
我需要select * from #temp order by num
/*
number
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
......(1000 行受影响)*/