好多的网友在SQL版问问题,一般我们要根据网友的数据自己手工创建表格,插入数据,本工具自动生成表格及数据,节省时间,才能抢到更多的分哦
比如:
我有如下数据:
ID Name Sdate
1 aa 2009 - 01 - 01
2 bb 2009 - 01 - 01
3 cc 2009 - 01 - 01
如何select出结果,当然不会有人提这个问题的,这只是一个假设
ID Name Sdate
1 aa 2009 - 01 - 01
2 bb 2009 - 01 - 01
3 cc 2009 - 01 - 01
如何select出结果,当然不会有人提这个问题的,这只是一个假设
使用本过程:
declare
@s
varchar
(
8000
)
set @s = ' Ta
ID Name Sdate
1 aa 2009-01-01
2 bb 2009-01-01
3 cc 2009-01-01
'
exec sp_autoscript @s
set @s = ' Ta
ID Name Sdate
1 aa 2009-01-01
2 bb 2009-01-01
3 cc 2009-01-01
'
exec sp_autoscript @s
就可以生成如下代码:
/**/
/*
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/
-- Test Data: Ta
If object_id ( ' Ta ' ) is not null
Drop table Ta
Go
Create table Ta(ID int ,Name varchar ( 2 ),Sdate smalldatetime )
Go
Insert into Ta
select 1 , ' aa ' , ' 2009-01-01 ' union all
select 2 , ' bb ' , ' 2009-01-01 ' union all
select 3 , ' cc ' , ' 2009-01-01 '
Go
-- Start
Select * from Ta
-- Result:
/**/ /*
*/
-- End
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/
-- Test Data: Ta
If object_id ( ' Ta ' ) is not null
Drop table Ta
Go
Create table Ta(ID int ,Name varchar ( 2 ),Sdate smalldatetime )
Go
Insert into Ta
select 1 , ' aa ' , ' 2009-01-01 ' union all
select 2 , ' bb ' , ' 2009-01-01 ' union all
select 3 , ' cc ' , ' 2009-01-01 '
Go
-- Start
Select * from Ta
-- Result:
/**/ /*
*/
-- End
是不是规范又快速呀,嘿嘿
部分内容网友自行修改,引用时请注明原作者
程序代码如下,欢迎大家指正(关于日期形如:yyyy-mm-dd HH:MM:ss 还在完善中,请关注.......)
-- 处理当前串中的空格,准备加入对时分秒的处理, 时间末处理。。。。。。
create function f_castdt( @s varchar ( 8000 ))
returns varchar ( 1000 )
as
begin
set @s = ltrim ( @s )
WHILE CHARINDEX ( ' ' , @s ) > 0
BEGIN
SET @s = replace ( @s , ' ' , ' ' )
END
return @s
end
go
-- 加一个分隔函数:
create function F_split(
@s varchar ( 8000 ), -- 包含多个数据项的字符串
@pos int , -- 要获取的数据项的位置
@split varchar ( 10 ) -- 数据分隔符
) RETURNS varchar ( 100 )
AS
BEGIN
IF @s IS NULL RETURN ( NULL )
DECLARE @splitlen int -- 分隔符长度
SELECT @splitlen = LEN ( @split + ' a ' ) - 2
WHILE @pos > 1 AND charindex ( @split , @s + @split ) > 0
SELECT @pos = @pos - 1 ,
@s = stuff ( @s , 1 , charindex ( @split , @s + @split ) + @splitlen , '' )
RETURN ( replace (( nullif ( left ( @s , charindex ( @split , @s + @split ) - 1 ), '' )), char ( 13 ), '' ))
END
GO
-- 判断当前串是有字母如果有那么当作varchar
create function f_ischar( @s varchar ( 100 ))
returns bit
as
begin
if patindex ( ' %[a-zA-Z]% ' , @s ) > 0
return 1
return 0
end
go
-- 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar ( 8000 ), -- 待分拆的字符串
@split varchar ( 20 ) -- 数据分隔符
) RETURNS @re TABLE (col varchar ( 100 ))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen = LEN ( @split + ' a ' ) - 2
WHILE CHARINDEX ( @split , @s ) > 0
BEGIN
INSERT @re VALUES ( rtrim ( ltrim ( LEFT ( @s , CHARINDEX ( @split , @s ) - 1 ))) )
SET @s = ltrim ( STUFF ( @s , 1 , CHARINDEX ( @split , @s ) + @splitlen , '' ))
END
INSERT @re VALUES ( @s )
RETURN
END
GO
create proc sp_autoscript
@s varchar ( 8000 )
as
set nocount on
declare @a varchar ( 8000 ), @b varchar ( 8000 ), @c varchar ( 8000 )
set @a = ltrim ( rtrim ( left ( @s , charindex ( char ( 13 ), @s ) - 1 )))
set @s = right ( @s , len ( @s ) - charindex ( char ( 13 ), @s ) - 1 )
set @b = ltrim ( rtrim ( left ( @s , charindex ( char ( 13 ), @s ) - 1 )))
set @s = right ( @s , len ( @s ) - charindex ( char ( 13 ), @s ) - 1 )
set @c = ltrim ( rtrim ( left ( @s , charindex ( char ( 13 ), @s ) - 1 )))
set @s = left ( @s , len ( @s ) - 1 )
create table #tmp1(px int identity ( 1 , 1 ),col varchar ( 120 ),col2 varchar ( 50 ),col3 varchar ( 50 ))
create table #tmp2(px int identity ( 1 , 1 ),col varchar ( 120 ))
create table #tmp3(px int identity ( 1 , 1 ),col varchar ( 1000 ))
insert into #tmp1(col) select ltrim ( rtrim (col)) from f_splitSTR( @b , ' ' )
insert into #tmp2 select ltrim ( rtrim (col)) from f_splitSTR( @c , ' ' )
insert into #tmp3 select ltrim ( rtrim (col)) from f_splitSTR( @s , '
' )
-- 取每一列的最大值,尽量让类型准确
-- update a
-- set col= c.col
-- from #tmp2 a left join (
-- select a.px, max(dbo.F_split(b.col,a.px,' ')) as col
-- from #tmp2 a,#tmp3 b
-- group by a.px) c on a.px = c.px
declare @px int , @col varchar ( 100 ), @maxid int
select @maxid = count ( 1 ) from #tmp1
DECLARE f CURSOR FOR SELECT px,col FROM #tmp2
OPEN f
FETCH NEXT FROM f INTO @px , @col
-- IF @@FETCH_STATUS <> 0
WHILE @@FETCH_STATUS = 0
BEGIN
if ISDATE ( @col ) = 1
if len ( @col ) <= 10
update #tmp1
set col = col + ' smalldatetime, ' ,
col2 = case when px = 1 then ' select ''''' else '''''' end ,
col3 = case when px = @maxid then ''''' union all ' else ''''' , ' end
where px = @px
else
update #tmp1
set col = col + ' datetime, ' ,
col2 = case when px = 1 then ' select ''''' else '''''' end ,
col3 = case when px = @maxid then ''''' union all ' else ''''' , ' end
where px = @px
else
if isnumeric ( @col ) = 1
if charindex ( ' . ' , @col ) > 0
update #tmp1
set col = col + ' numeric( ' + ltrim ( len ( @col ) - 1 ) + ' , ' + ltrim ( len ( @col ) - charindex ( ' . ' , @col )) + ' ), ' ,
col2 = case when px = 1 then ' select ' else '' end ,
col3 = case when px = @maxid then ' union all ' else ' , ' end
where px = @px
else
update #tmp1
set col = col + ' int, ' ,
col2 = case when px = 1 then ' select ' else '' end ,
col3 = case when px = @maxid then ' union all ' else ' , ' end
where px = @px
else
if dbo.f_ischar( @col ) = 1
update #tmp1
set col = col + ' varchar( ' + ltrim ( len ( @col )) + ' ), ' ,
col2 = case when px = 1 then ' select ''''' else '''''' end ,
col3 = case when px = @maxid then ''''' union all ' else ''''' , ' end
where px = @px
FETCH NEXT FROM f INTO @px , @col
END
CLOSE f
DEALLOCATE f
update #tmp3
set col = dbo.f_castdt(col)
declare @sql varchar ( 8000 )
declare @sql1 varchar ( 8000 )
select @sql = isnull ( @sql + ' + ' , '' ) + ''''
+ col2 + ''' +dbo.F_split(col, ' + ltrim (px) + ' , '' '' ) ' + ' + ''' + col3 + ''''
from #tmp1
select @sql1 = isnull ( @sql1 , '' ) + col
from #tmp1
-- select @sql
-- if object_id('''+@a+''') is not null drop table '+@a+'+char(13)+
exec ( ' update #tmp3 set col = ' + @sql )
set @sql = ''
set @sql = ' /*
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/ ' + char ( 10 ) +
'
-- Test Data: ' + @a + char ( 10 ) +
' If object_id( ''' + @a + ''' ) is not null ' + char ( 13 ) +
' Drop table ' + @a + char ( 10 ) +
' Go ' + char ( 13 ) +
' Create table ' + @a + ' ( ' + left ( @sql1 , len ( @sql1 ) - 1 ) + ' ) ' + char ( 10 ) +
' Go '
+ char ( 10 ) +
' Insert into ' + @a + '
'
select @sql = @sql + col + char ( 13 ) from #tmp3
set @sql = left ( @sql , len ( @sql ) - 10 )
set @sql = @sql + char ( 13 ) +
' Go ' + char ( 10 ) +
' --Start ' + char ( 10 ) +
' Select * from ' + @a + char ( 10 ) + '
--Result:
/*
*/
--End '
print @sql
drop table #tmp1,#tmp2,#tmp3
set nocount off
go
-- -- 调用
declare @s varchar ( 8000 )
set @s = ' Ta
ID Name Sdate
1 aa 2009-01-01
2 bb 2009-01-01
3 cc 2009-01-01
'
exec sp_autoscript @s
-- -- 清除
drop function f_splitSTR,f_ischar,f_castdt,F_split
drop proc sp_autoscript