下面的SQL我想先插入0-999999 select top 999999 id=identity( int,0,1) into tmp from syscolumns a,syscolumns b --这句执行 (528529 行受影响),我想应该是999999行受影响 是什么问题select id1,left(newid(),6) xx into # from
(
select id1 = cast(id as varchar) , id from tmp
union all
select id1 = right('0' + cast(id as varchar),5) , id from tmp
union all
select id1 = right('00' + cast(id as varchar),5) , id from tmp where id < 1000
union all
select id1 = right('000' + cast(id as varchar),5) , id from tmp where id < 100
union all
select id1 = right('0000' + cast(id as varchar),5) , id from tmp where id < 10
) t
order by id , len(id1)
drop table tmpselect * from tmp
(
select id1 = cast(id as varchar) , id from tmp
union all
select id1 = right('0' + cast(id as varchar),5) , id from tmp
union all
select id1 = right('00' + cast(id as varchar),5) , id from tmp where id < 1000
union all
select id1 = right('000' + cast(id as varchar),5) , id from tmp where id < 100
union all
select id1 = right('0000' + cast(id as varchar),5) , id from tmp where id < 10
) t
order by id , len(id1)
drop table tmpselect * from tmp
from syscolumns a,syscolumns b,syscolumns c select id1,left(newid(),6) as xx
into #
from
(
select id1 = cast(id as varchar) , id from tmp
union all
select id1 = right( '0 ' + cast(id as varchar),5) , id from tmp
union all
select id1 = right( '00 ' + cast(id as varchar),5) , id from tmp where id < 1000
union all
select id1 = right( '000 ' + cast(id as varchar),5) , id from tmp where id < 100
union all
select id1 = right( '0000 ' + cast(id as varchar),5) , id from tmp where id < 10
) t
order by id , len(id1)
select * from tmp select max(id),min(id) from tmp drop table tmp ,#
解决方法
1,用 while 循环
2,多连几次表 .比如 from syscolumns,syscolumns,syscolumns
这样效率很差,因为连表结果行数不可控制.虽然用了 top ..
总数
-----------
1000000(所影响的行数为 1 行)
*/
总数 最小 最大
----------- -------------------- --------------------
1000000 0 999999(所影响的行数为 1 行)*/
----------------------
不行的,关键是syssolunms的行数决定了最后的最大行数
长度为 4 个字节,存储从 -2.147.483.648 到 2.147.483.647 的数字。999999没有超出 INT 的范围主要是syssolunms的行数决定了最后的最大行数