if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO --分段截取函数(邹建) CREATE FUNCTION dbo.f_GetStr( @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(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),'')) END GO --> 生成测试数据表: syslogd IF NOT OBJECT_ID('[syslogd]') IS NULL DROP TABLE [syslogd] GO CREATE TABLE [syslogd]([MsgDate] DATETIME,[MsgTime] DATETIME,[MsgText] NVARCHAR(90)) INSERT [syslogd] SELECT N'04-23-2010','09:51:41',N'<PNB0>tvkoo.udp 1272015958-1272016133 172.16.10.23:17-220.167.212.11:50 597 60' UNION ALL SELECT N'04-23-2010','09:51:41',N'<PNB0>default.udp 1272016012-1272016133 172.17.36.72:6048-221.6.235.6:22757 42 0' UNION ALL SELECT N'04-23-2010','09:51:41',N'<PNB0>default.udp 1272016012-1272016133 172.17.36.72:6051-124.160.125.5:1814 41 0' UNION ALL SELECT N'04-23-2010','09:51:41',N'<PNB0>http.tcp 1272016068-1272016133 172.16.16.18:238-19.42.233.243:80 970 241' GO -->目标数据表结构如下(请执行sql): select MsgDate, MsgTime, dbo.f_GetStr([MsgText],1,' ') MsgText_A, dbo.f_GetStr([MsgText],2,' ') MsgText_B, dbo.f_GetStr([MsgText],3,' ') MsgText_C, dbo.f_GetStr([MsgText],4,' ') MsgText_D, dbo.f_GetStr([MsgText],5,' ') MsgText_E FROM syslogd--以下是我根据自己的需要写的代码,请高手帮忙修改错误 declare @tbName varchar(100),@sql varchar(2000),@date datetime --获取当前日期形成日期字符串 select @tbName = convert(varchar(10),getdate(),120) select @tbName = substring(@tbName,1,4)+substring(@tbName,6,2)+substring(@tbName,9,2) /* select @sql='select * into syslogd_'+@tbName+' from syslogd' exec(@sql) 这个语句在查询分析器执行就没有问题 而下面的于具有错误 */ select @sql='select MsgDate, MsgTime, dbo.f_GetStr([MsgText],1,'' '') MsgText_A, dbo.f_GetStr([MsgText],2,'' '') MsgText_B, dbo.f_GetStr([MsgText],3,'' '') MsgText_C, dbo.f_GetStr([MsgText],4,'' '') MsgText_D, dbo.f_GetStr([MsgText],5,'' '') MsgText_E into syslogd_'+@tbName+' FROM syslogd' exec(@sql) --在新形成的表的MsgText_C字段建立索引(不清楚是否有错误) exec('CREATE INDEX [IX_syslogd] ON [dbo].[syslogd_'+@tbName+']([MsgText_C]) ON [PRIMARY]') GO
exec('CREATE INDEX [IX_syslogd] ON [dbo].[syslogd_'+@tbName+']([MsgText_C]) ON [PRIMARY]') up...
drop function [dbo].[f_GetStr]
GO
--分段截取函数(邹建)
CREATE FUNCTION dbo.f_GetStr(
@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(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
--> 生成测试数据表: syslogd
IF NOT OBJECT_ID('[syslogd]') IS NULL
DROP TABLE [syslogd]
GO
CREATE TABLE [syslogd]([MsgDate] DATETIME,[MsgTime] DATETIME,[MsgText] NVARCHAR(90))
INSERT [syslogd]
SELECT N'04-23-2010','09:51:41',N'<PNB0>tvkoo.udp 1272015958-1272016133 172.16.10.23:17-220.167.212.11:50 597 60' UNION ALL
SELECT N'04-23-2010','09:51:41',N'<PNB0>default.udp 1272016012-1272016133 172.17.36.72:6048-221.6.235.6:22757 42 0' UNION ALL
SELECT N'04-23-2010','09:51:41',N'<PNB0>default.udp 1272016012-1272016133 172.17.36.72:6051-124.160.125.5:1814 41 0' UNION ALL
SELECT N'04-23-2010','09:51:41',N'<PNB0>http.tcp 1272016068-1272016133 172.16.16.18:238-19.42.233.243:80 970 241'
GO
-->目标数据表结构如下(请执行sql):
select MsgDate, MsgTime,
dbo.f_GetStr([MsgText],1,' ') MsgText_A,
dbo.f_GetStr([MsgText],2,' ') MsgText_B,
dbo.f_GetStr([MsgText],3,' ') MsgText_C,
dbo.f_GetStr([MsgText],4,' ') MsgText_D,
dbo.f_GetStr([MsgText],5,' ') MsgText_E
FROM syslogd--以下是我根据自己的需要写的代码,请高手帮忙修改错误
declare @tbName varchar(100),@sql varchar(2000),@date datetime
--获取当前日期形成日期字符串
select @tbName = convert(varchar(10),getdate(),120)
select @tbName = substring(@tbName,1,4)+substring(@tbName,6,2)+substring(@tbName,9,2)
/*
select @sql='select * into syslogd_'+@tbName+' from syslogd'
exec(@sql)
这个语句在查询分析器执行就没有问题
而下面的于具有错误
*/
select @sql='select MsgDate, MsgTime,
dbo.f_GetStr([MsgText],1,'' '') MsgText_A,
dbo.f_GetStr([MsgText],2,'' '') MsgText_B,
dbo.f_GetStr([MsgText],3,'' '') MsgText_C,
dbo.f_GetStr([MsgText],4,'' '') MsgText_D,
dbo.f_GetStr([MsgText],5,'' '') MsgText_E
into syslogd_'+@tbName+' FROM syslogd'
exec(@sql)
--在新形成的表的MsgText_C字段建立索引(不清楚是否有错误)
exec('CREATE INDEX [IX_syslogd] ON [dbo].[syslogd_'+@tbName+']([MsgText_C]) ON [PRIMARY]')
GO
up...
select @sql='select MsgDate, MsgTime,
dbo.f_GetStr([MsgText],1,'' '') MsgText_A,
dbo.f_GetStr([MsgText],2,'' '') MsgText_B,
dbo.f_GetStr([MsgText],3,'' '') MsgText_C,
dbo.f_GetStr([MsgText],4,'' '') MsgText_D,
dbo.f_GetStr([MsgText],5,'' '') MsgText_E
into syslogd_'+@tbName+' FROM '+@tbName
exec(@sql)也就是我那个划分字段以前的表名字是按照日期动态形成,划分后也是动态形成的
1楼htl258给的答案是正确的,我现在把syslogd改成+@tbName又出错了,真是郁闷了
select @sql='select MsgDate, MsgTime,
dbo.f_GetStr([MsgText],1,'' '') MsgText_A,
dbo.f_GetStr([MsgText],2,'' '') MsgText_B,
dbo.f_GetStr([MsgText],3,'' '') MsgText_C,
dbo.f_GetStr([MsgText],4,'' '') MsgText_D,
dbo.f_GetStr([MsgText],5,'' '') MsgText_E
into ['+@tbName+'] FROM '+@tbName
exec(@sql)
dbo.f_GetStr([MsgText],1,'' '') MsgText_A,
dbo.f_GetStr([MsgText],2,'' '') MsgText_B,
dbo.f_GetStr([MsgText],3,'' '') MsgText_C,
dbo.f_GetStr([MsgText],4,'' '') MsgText_D,
dbo.f_GetStr([MsgText],5,'' '') MsgText_E
into ['+@tbName+'] FROM '+quotename(@tbName)
exec(@sql)
declare @tbName varchar(100),@sql varchar(2000),@date datetime
--获取当前日期形成日期字符串
select @tbName = convert(varchar(10),getdate(),120)
select @tbName = substring(@tbName,1,4)+substring(@tbName,6,2)+substring(@tbName,9,2)
/*
select @sql='select * into syslogd_'+@tbName+' from syslogd'
exec(@sql)
这个语句在查询分析器执行就没有问题
而下面的于具有错误
*/
select @sql='select MsgDate, MsgTime,
dbo.f_GetStr([MsgText],1,'' '') MsgText_A,
dbo.f_GetStr([MsgText],2,'' '') MsgText_B,
dbo.f_GetStr([MsgText],3,'' '') MsgText_C,
dbo.f_GetStr([MsgText],4,'' '') MsgText_D,
dbo.f_GetStr([MsgText],5,'' '') MsgText_E
into syslogd_'+@tbName+' FROM '+quotename(@tbName)
exec(@sql)
--在新形成的表的MsgText_C字段建立索引(不清楚是否有错误)
exec('CREATE INDEX [IX_syslogd] ON [dbo].[syslogd_'+@tbName+']([MsgText_C]) ON [PRIMARY]')
GO上面可能有点理解错了,改为这样。