网上论坛生成测试数据方法(抢分利器)

在网上回复问题时,生成测试数据是必不可少的

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


*/

 

有特殊格式不支持可能不支持,大家有兴趣可方法上调整不能处理的格式

posted on 2008-06-19 11:13  中國風  阅读(190)  评论(0编辑  收藏  举报