SQL 快速生成测试数据
--数据 调用
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
