22,206
社区成员
发帖
与我相关
我的任务
分享
--首先创建split函数
Create FUNCTION [dbo].Split
(@SourceStr varchar(max),@Delimiter VARCHAR(20))--源字符串
RETURNS @table table(list varchar(2000) )
AS
BEGIN
SELECT @SourceStr=REPLACE(@SourceStr, @Delimiter,'$')
if charindex('$',@sourcestr)>0
begin
declare @i int
declare @n int
set @i=1
while charindex('$',@sourcestr,@i)>0
begin
set @n=charindex('$',@sourcestr,@i)
insert into @table values(substring(@sourcestr,@i, @n-@i) )
set @i=@n+1
end
insert into @table values(substring(@sourcestr,@i,len(@sourcestr)-@i+1))
end else insert into @table values(@sourcestr)
delete from @table where isnull(list,'') = ''
return
END
--再执行以下代码
DECLARE @hInstance INT,@ret INT,@hDOM INT,@hWEB INT,@hBody INT,@Text VARCHAR(2000),@err VARCHAR(200)
EXEC @ret=sp_oacreate 'MSXML2.XMLHTTP',@hInstance OUTPUT
EXEC @ret=sp_oamethod @hInstance,'open',NULL,'get','http://hq.sinajs.cn/list=sz000049','false'
EXEC @ret=sp_oamethod @hInstance,'send'
EXEC @ret=sp_oagetproperty @hInstance,'ResponseText',@text output
PRINT @ret
EXEC sp_oadestroy @hInstance
PRINT @text
SELECT * FROM [Split](@text,',') s
--输出
var hq_str_sz000049="德赛电池
25.39
25.43
24.53
25.39
24.48
24.52
24.53
1668160
41382219.60
2000
24.52
5400
24.51
11400
24.50
1500
24.49
13314
24.48
7501
24.53
1000
24.54
8620
24.55
201
24.56
2890
24.58
2011-01-14
15:06:02";
DECLARE @str NVARCHAR(4000)
SET @str = N'德赛电池,25.39,25.43,24.53,25.39,24.48,24.52,24.53,1668160,41382219.60,2000,24.52,5400,24.51,11400,24.50,1500,24.49,13314,24.48,7501,24.53,1000,24.54,8620,24.55,201,24.56,2890,24.58,2011-01-14,15:05:57'
EXEC insert_data_to_table @str
CREATE PROCEDURE insert_data_to_table
@str NVARCHAR(4000)
AS
begin
SET @str = @str + N','
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT '
WHILE(CHARINDEX(N',', @str) > 0)
BEGIN
DECLARE @temp NVARCHAR(4000)
SET @temp = SUBSTRING(@str,1, CHARINDEX(N',', @str)-1)
IF ISNUMERIC(@temp) = 1
SET @sql = @sql + @temp + N','
ELSE
SET @sql = @sql + N'''' +@temp + N''','
SET @str = STUFF(@str, 1, CHARINDEX(N',', @str), N'')
END
SET @sql = N'INSERT INTO table_name ' + SUBSTRING(@sql, 1, LEN(@sql)-1)
--PRINT @sql
EXEC (@sql)
END
DECLARE @str NVARCHAR(4000)
SET @str = '德赛电池,25.39,25.43,24.53,25.39,24.48,24.52,24.53,1668160,41382219.60,2000,24.52,5400,24.51,11400,24.50,1500,24.49,13314,24.48,7501,24.53,1000,24.54,8620,24.55,201,24.56,2890,24.58,2011-01-14,15:05:57'
EXEC insert_data_to_table @str
CREATE PROCEDURE insert_data_to_table
@str NVARCHAR(4000)
AS
begin
SET @str = @str + N','
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT '
WHILE(CHARINDEX(',', @str) > 0)
BEGIN
DECLARE @temp NVARCHAR(4000)
SET @temp = SUBSTRING(@str,1, CHARINDEX(N',', @str)-1)
IF ISNUMERIC(@temp) = 1
SET @sql = @sql + @temp + N','
ELSE
SET @sql = @sql + N'''' +@temp + N''','
SET @str = STUFF(@str, 1, CHARINDEX(',', @str), '')
END
SET @sql = N'INSERT INTO table_name ' + SUBSTRING(@sql, 1, LEN(@sql)-1)
--PRINT @sql
EXEC (@sql)
END