代码改变世界

SQL 快速生成测试数据

2010-01-03 14:30  曾祥展  阅读(478)  评论(0编辑  收藏

--数据 调用
declare@snvarchar(max)
set@s=N'#T
ID      NAME    DTIME
1 张 2007-12-15 
2 刘 2008-10-12 
3 王 2009-10-13 
4 赵 2009-12-15 
5 孙 2009-12-17 
6 于 2009-12-14 
7 李 2009-12-10 
8 高 2009-12-01 
9 金 2009-12-10 
'

exec #SQL_Script @s






ifobject_id('Tempdb..#SQL_Script') isnotnull
   
dropproc #SQL_Script
go

/****************************************************************************************************
%%臨時存儲過程名:#SQL_Script
%%參數:@s 分拆符串,@Flag=1 指定列名 @Flag=0 自動生成列名
%%功能:

%%備注@s格式:    空格以"~"替代,制表符以"`"替代,空值時以";"替代;用空格或制表符作為列的分隔,
                連續空格、制表符只計算一個,每一行結尾用不用輸入,如果在中間位置列為null時用";"替換

%%20080527 增加了表變量,臨時表,正式表判斷
             增加繁體簡體出現問號的問題。

%%20080612 增加整型準確判斷
*****************************************************************************************************
%%編寫:Ben   2008-05-19
****************************************************************************************************
*/
createproc #SQL_Script(@snvarchar(Max),@FlagBit=1)
as
declare@s2nvarchar(Max),@Tab sysname,@ColNamenvarchar(1000),@Printnvarchar(1000),@TabTypenvarchar(100)
,
@iint,@Colint,@TabIDint,@IsDateBit,@IsnumericBit,@Lenint,@MaxLenint,@NewIDBit
,
@CharBit,@Type sysname,@IsCharBit
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)--得到表名


ifobject_id('Tempdb..#') isnotnull
   
droptable #
createtable #(ID intidentity,Col nvarchar(Max))

ifright(@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,'')
whilepatindex('%['+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>0andleft(@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@Ttable(ID int,Col nvarchar(Max))

set nocount on
insert@Tselect ID,Col+char(9) from #

update # set Col=null


select@Col=0
if@Flag=0
   
set@ColName=''


while (selectmax(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(casewhencharindex(',',left(Col,charindex(char(9),Col)-1))>0orleft(Col,charindex(char(9),Col)-1) like'0[^.]%'   then0elseisnumeric(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(casewhenlen(left(Col,charindex(char(9),Col)-1))=36then1else0end),
           
@Char=max(casewhenleft(Col,charindex(char(9),Col)-1)='.'then1else0end),
           
@Col=@Col+1
   
from@T
   
where COl>''  andlen(COl)>1
   
andleft(Col,charindex(char(9),Col)-1) notin(';','Null')




if@IsDate=1
   
select  @Type='Datetime',@IsChar=1
elseIF@isnumeric=1and@Char=0
   
IF@Len>0
       
select@Type='decimal(18,'+rtrim(@Len-1)+')',@IsChar=0
   
else
       
select@Type='int',@IsChar=0
elseif@NewID=1
   
select@Type='uniqueidentifier',@IsChar=1
else
   
select@Type='nvarchar('+rtrim(@MaxLen)+')',@IsChar=1

update a
set Col=Coalesce(a.Col+',','')+case    when b.COl =''orleft(b.Col,charindex(char(9),b.Col)-1) in(';','null') then'null'
                                   
when  @IsChar=1thencasewhen@IsDate=0then'N'else''end+quotename(left(b.Col,charindex(char(9),b.Col)-1),'''')
                                   
elseleft(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@Tset Col=stuff(Col,1,charindex(char(9),Col),'') where COl>''  andlen(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 #

printreplicate(char(45)+char(45)+char(62)+char(32),2)+N'(Ben)生成測試數據'
print''
ifleft(@Tab,1)='@'
   
print'declare '+@Tab+' table('+@ColName+')'
else
   
begin
       
ifleft(@Tab,1)='#'
           
set@TabType='Tempdb..'
       
else
           
set@TabType=''
       
print'if not object_id('''+@TabType+@Tab+''') is null'
       
printchar(9)+'drop table '+@Tab
       
print'Go'
       
print   'Create table '+@Tab+'('+@ColName+')'
   
end
print'Insert '+@Tab
whileexists(select1from #)
begin
   
select@Print='select '+Col+casewhen ID=@ithen''else' union all'endfrom # where ID=@Col
   
delete # where ID=@Col
   
print@Print
   
set@COl=@COl+1
end
ifleft(@Tab,1)<>'@'
print'Go'
else
print''
print'Select * from '+@Tab


go