SQL 快速生成测试数据

-- 数据 调用
declare @s nvarchar ( 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  






if object_id ( ' Tempdb..#SQL_Script ' )  is not null
     drop proc  #SQL_Script
go

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

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

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

%%20080612 增加整型準確判斷
*****************************************************************************************************
%%編寫：Ben   2008-05-19
**************************************************************************************************** */
create proc  #SQL_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 = ' Datetime ' , @IsChar = 1
else IF @isnumeric = 1 and @Char = 0
     IF @Len > 0
         select @Type = ' decimal(18, ' + rtrim ( @Len - 1 ) + ' ) ' , @IsChar = 0
     else  
         select @Type = ' int ' , @IsChar = 0
else if @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  = '' 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 replicate ( char ( 45 ) + char ( 45 ) + char ( 62 ) + char ( 32 ), 2 ) + N ' (Ben)生成測試數據 '
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( ''' + @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

    本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638243.html，如需转载请自行联系原作者