网上论坛生成测试数据方法（抢分利器）
在网上回复问题时，生成测试数据是必不可少的
1.在master系统库生成存储过程方法
USE master go IF object_id('SP_Script') is not null drop proc SP_Script go /**************************************************************************************************** %%存儲過程名：SP_Script %%參數:@s 分拆符串,@Flag=1 指定列名 @Flag=0 自動生成列名 %%功能: %%備注@s格式: 空格以"~"替代,制表符以"`"替代,空值時以";"替代;用空格或制表符作為列的分隔， 連續空格、制表符只計算一個,每一行結尾用不用輸入,如果在中間位置列為null時用";"替換 %%20080527 增加了表變量，臨時表，正式表判斷 增加繁體簡體出現問號的問題。 ***************************************************************************************************** %%編寫：Roy 2008-05-19 *****************************************************************************************************/ create proc SP_Script(@s nvarchar(Max),@Flag Bit=1) as declare @s2 nvarchar(Max),@Tab sysname,@ColName nvarchar(1000),@Print nvarchar(1000),@TabType nvarchar(100) ,@i int,@Col int,@TabID int,@IsDate Bit,@Isnumeric Bit,@Len int,@MaxLen int,@NewID Bit ,@Char Bit,@Type sysname,@IsChar Bit select @i=charindex(char(13)+char(10),@s),@Tab=rtrim(left(@s,@i-1)),@s=stuff(@s,1,@i+1,''), @Tab=left(@Tab,len(@Tab)-patindex('%[^'+char(9)+char(32)+']%',reverse(@Tab))+1)--得到表名 if object_id('Tempdb..#') is not null drop table # create table #(ID int identity,Col nvarchar(Max)) if right(@s,2)!=char(13)+char(10) set @s=@s+char(13)+char(10) --替換中間空格為一個 select @i=patindex('%'+char(32)+char(32)+'%',@s) while @i>0 select @s=replace(@s,char(32)+char(32),char(32)),@i=patindex('%'+char(32)+char(32)+'%',@s) --替換中間空格+制表符為一個制表符 select @i=patindex('%'+char(32)+char(9)+'%',@s) while @i>0 select @s=replace(@s,char(32)+char(9),char(9)),@i=patindex('%'+char(32)+char(9)+'%',@s) --替換中間制表符+空格為一個制表符 select @i=patindex('%'+char(9)+char(32)+'%',@s) while @i>0 select @s=replace(@s,char(9)+char(32),char(9)),@i=patindex('%'+char(9)+char(32)+'%',@s) --去掉每一行開始的空格 select @i=patindex('%'+char(13)+char(10)+char(32)+'%',@s) while @i>0 select @s=replace(@s,char(13)+char(10)+char(32),char(13)+char(10)),@i=patindex('%'+char(13)+char(10)+char(32)+'%',@s) --去掉每一行結束的空格 select @i=patindex('%'+char(32)+char(13)+char(10)+'%',@s) while @i>0 select @s=replace(@s,char(32)+char(13)+char(10),char(13)+char(10)),@i=patindex('%'+char(32)+char(13)+char(10)+'%',@s) --去掉每一行開始的制表符 select @i=patindex('%'+char(13)+char(10)+char(9)+'%',@s) while @i>0 select @s=replace(@s,char(13)+char(10)+char(9),char(13)+char(10)),@i=patindex('%'+char(13)+char(10)+char(9)+'%',@s) --去掉每一行結束的制表符 select @i=patindex('%'+char(9)+char(13)+char(10)+'%',@s) while @i>0 select @s=replace(@s,char(9)+char(13)+char(10),char(13)+char(10)),@i=patindex('%'+char(9)+char(13)+char(10)+'%',@s) --替換空格為制表符 select @s=replace(@s,char(32),char(9)) --替換中間制表符為1個 select @i=patindex('%'+char(9)+char(9)+'%',@s) while @i>0 select @s=replace(@s,char(9)+char(9),char(9)),@i=patindex('%'+char(9)+char(9)+'%',@s) if @Flag=1 begin select @i=charindex(char(13)+char(10),@s),@ColName=rtrim(left(@s,@i-1))+char(9),@s=stuff(@s,1,@i+1,'') while patindex('%['+char(9)+char(32)+']%',@ColName)=1 select @ColName=stuff(@ColName,1,1,'') end select @i=charindex(char(13)+char(10),@s),@s=@s+char(13)+char(10) while @i>0 and left(@s,@i-1)>'' begin select @s2=quotename(left(@s,@i-1),'''') ,@s=stuff(@s,1,charindex(char(13)+char(10),@s)+1,''),@i=charindex(char(13)+char(10),@s) set nocount on exec('insert # select N'+@s2) end --取列的類型 declare @T table(ID int,Col nvarchar(Max)) set nocount on insert @T select ID,Col+char(9) from # update # set Col=null select @Col=0 if @Flag=0 set @ColName='' while (select max(len(Col)) from @T)>0 begin select @IsDate=min(isdate(replace(replace(left(Col,charindex(char(9),Col)-1),'~',char(32)),'`',char(9)))), @isnumeric=min(case when charindex(',',left(Col,charindex(char(9),Col)-1))>0 or left(Col,charindex(char(9),Col)-1) like '0[^.]%' then 0 else isnumeric(left(Col,charindex(char(9),Col)-1))end), @Len=max(Coalesce(len(right(left(Col,charindex(char(9),Col)-1),charindex('.',reverse(left(Col,charindex(char(9),Col)-1))))),0)), @MaxLen=max(len(left(Col,charindex(char(9),Col)-1))), @NewID=min(case when len(left(Col,charindex(char(9),Col)-1))=36 then 1 else 0 end), @Char=max(case when left(Col,charindex(char(9),Col)-1)='.' then 1 else 0 end), @Col=@Col+1 from @T where COl>'' and len(COl)>1 and left(Col,charindex(char(9),Col)-1) not in(';','Null') if @IsDate=1 select @Type=CASE WHEN EXISTS(SELECT 1 FROM @T WHERE Col LIKE '%~%') THEN 'Datetime' ELSE 'Date' END,@IsChar=1 else IF @isnumeric=1 and @Char=0 IF @Len>0 select @Type='decimal(18,'+rtrim(@Len+5)+')',@IsChar=0 else select @Type='int',@IsChar=0 else if @NewID=1 select @Type='uniqueidentifier',@IsChar=1 else select @Type='nvarchar('+rtrim(@MaxLen+20)+')',@IsChar=1 update a set Col=Coalesce(a.Col+',','')+case when b.COl ='' or left(b.Col,charindex(char(9),b.Col)-1) in(';','null') then 'null' when @IsChar=1 then case when @IsDate=0 then 'N' else '' end +quotename(left(b.Col,charindex(char(9),b.Col)-1),'''') else left(b.Col,charindex(char(9),b.Col)-1) end from # a join @T b on a.ID=b.ID if @Flag=1 set @ColName=stuff(@ColName,charindex(char(9),@ColName),1,']'+char(32)+@Type+',[') else set @ColName=@ColName+',[Col'+rtrim(@Col)+'] '+@Type update @T set Col=stuff(Col,1,charindex(char(9),Col),'') where COl>'' and len(COl)>1 end if @Flag=1 set @ColName='['+left(@ColName,len(@ColName)-2) else set @ColName=stuff(@ColName,1,1,'') update # set Col=replace(replace(COl,'~',char(32)),'`',char(9)) set @ColName=replace(replace(@ColName,'~',char(32)),'`',char(9)) select @Col=1,@i=max(ID) from # print 'use Tempdb' print 'go' print replicate(char(45)+char(45)+char(62)+char(32),2)--+N'(Roy)生成測試數據' print '' if left(@Tab,1)='@' print 'declare '+@Tab+' table('+@ColName+')' else begin if left(@Tab,1)='#' set @TabType='Tempdb..' else set @TabType='' print 'if not object_id(N'''+@TabType+@Tab+''') is null' print char(9)+'drop table '+@Tab print 'Go' print 'Create table '+@Tab+'('+@ColName+')' end print 'Insert '+@Tab while exists(select 1 from #) begin select @Print='select '+Col+case when ID=@i then '' else ' union all' end from # where ID=@Col delete # where ID=@Col print @Print set @COl=@COl+1 end if left(@Tab,1)<>'@' print 'Go' else print ' ' print 'Select * from '+@Tab GO exec sp_ms_marksystemobject 'SP_Script'--添加在系统存储过程目录
2.调用方法
空格时要用~符号填充
2.1、临时表
e.g.
declare @s nvarchar(max) set @s=N'#T id value time meterId 1 10 2016-01-01~08:00 m0001 2 11 2016-01-03~08:00 m0001 3 20 2016-01-06~08:00 m0002 4 30 2016-01-07~08:00 m0002 5 40 2016-01-09~08:00 m0002 ' exec SP_Script @s /* use Tempdb go --> --> if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([id] int,[value] int,[time] Datetime,[meterId] nvarchar(25)) Insert #T select 1,10,'2016-01-01 08:00',N'm0001' union all select 2,11,'2016-01-03 08:00',N'm0001' union all select 3,20,'2016-01-06 08:00',N'm0002' union all select 4,30,'2016-01-07 08:00',N'm0002' union all select 5,40,'2016-01-09 08:00',N'm0002' Go Select * from #T */
2.2、表变量
e.g.
declare @s nvarchar(max) set @s=N'@T id value time meterId 1 10 2016-01-01~08:00 m0001 2 11 2016-01-03~08:00 m0001 3 20 2016-01-06~08:00 m0002 4 30 2016-01-07~08:00 m0002 5 40 2016-01-09~08:00 m0002 ' exec SP_Script @s /* use Tempdb go --> --> declare @T table([id] int,[value] int,[time] Datetime,[meterId] nvarchar(25)) Insert @T select 1,10,'2016-01-01 08:00',N'm0001' union all select 2,11,'2016-01-03 08:00',N'm0001' union all select 3,20,'2016-01-06 08:00',N'm0002' union all select 4,30,'2016-01-07 08:00',N'm0002' union all select 5,40,'2016-01-09 08:00',N'm0002' Select * from @T */
3.3、表
declare @s nvarchar(max) set @s=N'T id value time meterId 1 10 2016-01-01~08:00 m0001 2 11 2016-01-03~08:00 m0001 3 20 2016-01-06~08:00 m0002 4 30 2016-01-07~08:00 m0002 5 40 2016-01-09~08:00 m0002 ' exec SP_Script @s /* use Tempdb go --> --> if not object_id(N'T') is null drop table T Go Create table T([id] int,[value] int,[time] Datetime,[meterId] nvarchar(25)) Insert T select 1,10,'2016-01-01 08:00',N'm0001' union all select 2,11,'2016-01-03 08:00',N'm0001' union all select 3,20,'2016-01-06 08:00',N'm0002' union all select 4,30,'2016-01-07 08:00',N'm0002' union all select 5,40,'2016-01-09 08:00',N'm0002' Go Select * from T */
有特殊格式不支持可能不支持，大家有兴趣可方法上调整不能处理的格式
