create table t(id int,name varchar(10),sex varchar(10)) gocreate proc sp_wsp @i int --生成多少行 as insert into t select t.* from t full join master..spt_values b on 1=1 where b.type='p' and b.number between 1 and @i go--调用 exec sp_wsp 20
select '' as spaceLine union all select '' union all select '' union all select '' union all select ''
那就直接这样:declare @i int set @I=10 select t.* from 表名 t full join master..spt_values b on 1=1 where b.type='p' and b.number between 1 and @i
#3是个方法,你也可以写循环啊declare @i int begin while @i < 1000 insert into '' set @i =@i + 1 end
参考: CREATE TABLE #tp ( pid VARCHAR(10), title VARCHAR(10) ) INSERT INTO #tp SELECT 1 ,'aaa' INSERT INTO #tp SELECT 2 ,'bbb' INSERT INTO #tp SELECT 1 ,'ccc' INSERT INTO #tp SELECT 10 ,'ddd' INSERT INTO #tp SELECT 11 ,'eee'SELECT * FROM #tp tDECLARE @intCount INT SET @intCount=(SELECT COUNT(*) FROM #tp) SELECT * INTO #td FROM #tp WHERE 1=2 WHILE @intCount>0 BEGIN INSERT INTO #td VALUES('','') SET @intCount=@intCount-1 END SELECT * FROM #tp t SELECT * FROM #TDpid title ---------- ---------- 1 aaa 2 bbb 1 ccc 10 ddd 11 eee(5 row(s) affected)pid title ---------- ----------
(5 row(s) affected)
--那就这样:create proc sp_wsp @i int as select top 0 * into # from t select #.* from # full join master..spt_values b on 1=1 where b.type='p' and b.number between 1 and @i goexec sp_wsp 10
无论哪种方法 是否只能用 null as fieldname,null as fieldname,....null as fieldname,一个字段一个字段的设置为空???
是的。还有null并不是空,请分清了。
select replace(columnname1,columnname1,' ') as columnname1, replace(columnname2,columnname2,' ') as columnname2 into newtable from oldtable
SELECT b.* FROM [master].dbo.spt_values a LEFT JOIN 表 b ON a.name = NULL WHERE a.[type] = 'P' AND a.number < 10生成10行空行,最大2048,超过2048 [master].dbo.spt_values 这个辅表换了即可
然后在插入空行?
刚看完这篇文章,你就来一个如题。。
create table t(id int,name varchar(10),sex varchar(10))
gocreate proc sp_wsp
@i int --生成多少行
as
insert into t select t.* from t full join master..spt_values b
on 1=1
where b.type='p' and b.number between 1 and @i
go--调用
exec sp_wsp 20
select '' union all
select '' union all
select '' union all
select ''
那就直接这样:declare @i int
set @I=10
select t.* from 表名 t full join master..spt_values b
on 1=1
where b.type='p' and b.number between 1 and @i
begin
while @i < 1000
insert into ''
set @i =@i + 1
end
CREATE TABLE #tp
(
pid VARCHAR(10),
title VARCHAR(10)
)
INSERT INTO #tp SELECT 1 ,'aaa'
INSERT INTO #tp SELECT 2 ,'bbb'
INSERT INTO #tp SELECT 1 ,'ccc'
INSERT INTO #tp SELECT 10 ,'ddd'
INSERT INTO #tp SELECT 11 ,'eee'SELECT * FROM #tp tDECLARE @intCount INT
SET @intCount=(SELECT COUNT(*) FROM #tp)
SELECT * INTO #td FROM #tp WHERE 1=2
WHILE @intCount>0
BEGIN
INSERT INTO #td VALUES('','')
SET @intCount=@intCount-1
END SELECT * FROM #tp t
SELECT * FROM #TDpid title
---------- ----------
1 aaa
2 bbb
1 ccc
10 ddd
11 eee(5 row(s) affected)pid title
---------- ----------
(5 row(s) affected)
--那就这样:create proc sp_wsp
@i int
as
select top 0 * into # from t
select #.* from # full join master..spt_values b
on 1=1
where b.type='p' and b.number between 1 and @i
goexec sp_wsp 10
是否只能用 null as fieldname,null as fieldname,....null as fieldname,一个字段一个字段的设置为空???
replace(columnname2,columnname2,' ') as columnname2
into newtable from oldtable
SELECT b.* FROM
[master].dbo.spt_values a LEFT JOIN 表 b ON a.name = NULL
WHERE a.[type] = 'P' AND a.number < 10生成10行空行,最大2048,超过2048 [master].dbo.spt_values 这个辅表换了即可