数据库字段CREATE TABLE [dbo].[tb_four_in_one] (
[id] [int] NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[title] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[downURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[desc] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[downLength] [int] NULL ,
[lyric] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[tryURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[tryLength] [int] NULL 想把文本中的数据导入此表
文本中是这样的[info0]
author=陈浩峰
desc=
downLength=896234
downURL=D:/Res/res3//music/albums/79/399949975780.mp3
id=39994
lyric=
title=万世师表开场组曲 (纯音乐)
tryLength=896234
tryURL=D:/Res/res3//music/albums/79/399949975780.mp3
type=music[info2]
desc=韩国车展惹人眼球的美女模特
downLength=880636
downURL=D:/Res/res4/video/4007/20090817113840674.3gp
id=12003811
title=惹人眼球的美女模特
type=video[info3]
desc=殷尚恭是霸王
downLength=124111
downURL=D:/Res/res1/txt/3005/20090330161228196.txt
id=1502743
title=擒情贵公子
type=article[info4]
desc=
downLength=22756
downURL=D:/Res/res3/img/2017/20090519111859198.gif
id=1008905
title=可爱
type=img
从txt文件读取上述文本,每一个[info]就是插入表的一条数据 有什么好的方法吗?
在线急等.....
[id] [int] NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[title] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[downURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[desc] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[downLength] [int] NULL ,
[lyric] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[tryURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[tryLength] [int] NULL 想把文本中的数据导入此表
文本中是这样的[info0]
author=陈浩峰
desc=
downLength=896234
downURL=D:/Res/res3//music/albums/79/399949975780.mp3
id=39994
lyric=
title=万世师表开场组曲 (纯音乐)
tryLength=896234
tryURL=D:/Res/res3//music/albums/79/399949975780.mp3
type=music[info2]
desc=韩国车展惹人眼球的美女模特
downLength=880636
downURL=D:/Res/res4/video/4007/20090817113840674.3gp
id=12003811
title=惹人眼球的美女模特
type=video[info3]
desc=殷尚恭是霸王
downLength=124111
downURL=D:/Res/res1/txt/3005/20090330161228196.txt
id=1502743
title=擒情贵公子
type=article[info4]
desc=
downLength=22756
downURL=D:/Res/res3/img/2017/20090519111859198.gif
id=1008905
title=可爱
type=img
从txt文件读取上述文本,每一个[info]就是插入表的一条数据 有什么好的方法吗?
在线急等.....
/** 导入文本文件 */
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'
可以修改你的格式如下面的
BULK INSER(大容量文本导入)txt文件内容:数据|数据|数据
数据|数据|数据现在要将这导入到表中.结果是
col1 col2 col3
数据 数据 数据
数据 数据 数据
语句:BULK INSERT 表名
FROM 'C:\name.txt'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR='\r\n',
DATAFILETYPE='char'
);
author=陈浩峰
desc=
downLength=896234
downURL=D:/Res/res3//music/albums/79/399949975780.mp3
id=39994
lyric=
title=万世师表开场组曲 (纯音乐)
tryLength=896234
tryURL=D:/Res/res3//music/albums/79/399949975780.mp3
type=music
[info2]
desc=韩国车展惹人眼球的美女模特
downLength=880636
downURL=D:/Res/res4/video/4007/20090817113840674.3gp
id=12003811
title=惹人眼球的美女模特
type=video[info3]
desc=殷尚恭是霸王
downLength=124111
downURL=D:/Res/res1/txt/3005/20090330161228196.txt
id=1502743
title=擒情贵公子
type=article[info4]
desc=
downLength=22756
downURL=D:/Res/res3/img/2017/20090519111859198.gif
id=1008905
title=可爱
type=img
CREATE TABLE #tmp(x varchar(MAX));
BULK INSERT #tmp
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR = '\r'
);DELETE #tmp WHERE x IS NULL;
;WITH Liang AS
(
SELECT rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()),x
FROM #tmp
),
Liang2 AS
(
SELECT *,groupid=(SELECT MAX(rowid) FROM Liang
WHERE rowid <= A.rowid
AND RTRIM(LTRIM(x)) LIKE '$[info%' ESCAPE '$')
FROM Liang AS A
)
SELECT
MAX(CASE WHEN x LIKE 'author%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS author,
MAX(CASE WHEN x LIKE 'desc%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS [desc],
MAX(CASE WHEN x LIKE 'downLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downLength,
MAX(CASE WHEN x LIKE 'downURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downURL,
MAX(CASE WHEN x LIKE 'id%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS id,
MAX(CASE WHEN x LIKE 'lyric%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS lyric,
MAX(CASE WHEN x LIKE 'title%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS title,
MAX(CASE WHEN x LIKE 'tryLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryLength,
MAX(CASE WHEN x LIKE 'tryURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryURL,
MAX(CASE WHEN x LIKE 'title%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS type
FROM Liang2 GROUP BY groupid;DROP TABLE #tmp;/*
author desc downLength downURL id lyric title tryLength tryURL type
陈浩峰 NULL 896234 D:/Res/res3//music/albums/79/399949975780.mp3 39994 NULL 万世师表开场组曲 (纯音乐) 896234 D:/Res/res3//music/albums/79/399949975780.mp3 万世师表开场组曲 (纯音乐)
NULL 韩国车展惹人眼球的美女模特 880636 D:/Res/res4/video/4007/20090817113840674.3gp 12003811 NULL 惹人眼球的美女模特 NULL NULL 惹人眼球的美女模特
NULL 殷尚恭是霸王 124111 D:/Res/res1/txt/3005/20090330161228196.txt 1502743 NULL 擒情贵公子 NULL NULL 擒情贵公子
NULL NULL 22756 D:/Res/res3/img/2017/20090519111859198.gif 1008905 NULL 可爱 NULL NULL 可爱
*/
BULK INSERT #tmp
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR = '\r'
);DELETE #tmp WHERE x IS NULL;SELECT rowid=IDENTITY(int,1,1),* INTO #myTemp1 FROM #tmp;SELECT *,groupid=(SELECT MAX(rowid) FROM #myTemp1
WHERE rowid <= A.rowid
AND RTRIM(LTRIM(x)) LIKE '$[info%' ESCAPE '$')
INTO #myTemp2
FROM #myTemp1 AS A;SELECT
MAX(CASE WHEN x LIKE 'author%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS author,
MAX(CASE WHEN x LIKE 'desc%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS [desc],
MAX(CASE WHEN x LIKE 'downLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downLength,
MAX(CASE WHEN x LIKE 'downURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downURL,
MAX(CASE WHEN x LIKE 'id%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS id,
MAX(CASE WHEN x LIKE 'lyric%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS lyric,
MAX(CASE WHEN x LIKE 'title%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS title,
MAX(CASE WHEN x LIKE 'tryLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryLength,
MAX(CASE WHEN x LIKE 'tryURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryURL,
MAX(CASE WHEN x LIKE 'type%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS type
FROM #myTemp2
GROUP BY groupid;--删除临时表
DROP TABLE #tmp,#myTemp1,#myTemp2;/*
author desc downLength downURL id lyric title tryLength tryURL type
陈浩峰 896234 D:/Res/res3//music/albums/79/399949975780.mp3 39994 万世师表开场组曲 (纯音乐) 896234 D:/Res/res3//music/albums/79/399949975780.mp3 music
NULL 韩国车展惹人眼球的美女模特 880636 D:/Res/res4/video/4007/20090817113840674.3gp 12003811 NULL 惹人眼球的美女模特 NULL NULL video
NULL 殷尚恭是霸王 124111 D:/Res/res1/txt/3005/20090330161228196.txt 1502743 NULL 擒情贵公子 NULL NULL article
NULL 22756 D:/Res/res3/img/2017/20090519111859198.gif 1008905 NULL 可爱 NULL NULL img
*/
服务器: 消息 170,级别 15,状态 1,行 1第 1 行: 'MAX' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 12
第 12 行: ';' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 14
'ROW_NUMBER' 不是可以识别的 函数名。在2005里面运行能过么???2000里我都不知道怎么改!!!!麻烦能帮改下不??急迫!!!!!
汗.... 我是通过dom4j把XML文件写入到txt文件的。。 <?xml version="1.0" encoding="gb2312" ?>
<info type="video" id="12002917" title="SJ新人气单曲-就是你" downURL="http://res4.mmlive.cn/video/4005/2009062616303882.3gp" desc="SuperJunior - 就是你It's you(中字)" downLength="953137" />
汗.XML多好分析呀..还整成txt..
你自己INSERT就行了.INSERT INTO xx(列名,列名)
SELECT
MAX(xxxx).....
FROM #myTemp2
GROUP BY groupid;就可以了..当然.你要自己去改改列的顺序.我SELECT的时候.没有按你的表的列的顺序写..
这些你自己去改就行了.
[id] [int] NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[title] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[downURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[desc] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[downLength] [int] NULL ,
[lyric] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[tryURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[tryLength] [int] NULL
)create table #t(col varchar(8000))BULK
INSERT #t
FROM 'C:\test.txt'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)select tid=identity(int,1,1),* into #t1 from #t select gid=identity(int,1,1),a.tid as tid1,min(b.tid) as tid2
into #t2
from #t1 a
left join #t1 b on a.tid<b.tid
where a.col like '%info%' and b.col like '%info%'
group by a.tidinsert tb_four_in_one(author,[desc],downLength,downURL,[id],lyric,title,tryLength,tryURL,type)
select
author=max(case when px=1 then right(col,len(col)-7) end),
[desc]=max(case when px=2 then right(col,len(col)-5) end),
downLength=max(case when px=3 then right(col,len(col)-11) end),
downURL=max(case when px=4 then right(col,len(col)-7) end),
[id]=max(case when px=5 then right(col,len(col)-3) end),
lyric=max(case when px=6 then right(col,len(col)-6) end),
title=max(case when px=7 then right(col,len(col)-7) end),
tryLength=max(case when px=8 then right(col,len(col)-10) end),
tryURL=max(case when px=9 then right(col,len(col)-7) end),
type=max(case when px=10 then right(col,len(col)-5) end)
from
(
select
gid=case when tid between b.tid1 and b.tid2 then gid end,
px=case when charindex('author=',col)>0 then 1
when charindex('desc=',col)>0 then 2
when charindex('downLength=',col)>0 then 3
when charindex('downURL=',col)>0 then 4
when charindex('id=',col)>0 then 5
when charindex('lyric=',col)>0 then 6
when charindex('title=',col)>0 then 7
when charindex('tryLength=',col)>0 then 8
when charindex('tryURL=',col)>0 then 9
when charindex('type=',col)>0 then 10
end,
a.*
from
#t1 a,#t2 b
where
a.tid between b.tid1 and b.tid2
) t
group by gid
select * from tb_four_in_one/**9994 usic 师表开场组曲 (纯音乐) D:/Res/res3//music/albums/79/399949975780.mp3 96234 浩峰 :/Res/res3//music/albums/79/399949975780.mp3 96234
2003811 ideo 眼球的美女模特 D:/Res/res4/video/4007/20090817113840674.3gp 国车展惹人眼球的美女模特 80636 NULL NULL NULL NULL
502743 rticle 贵公子 D:/Res/res1/txt/3005/20090330161228196.txt 尚恭是霸王 24111 NULL NULL NULL NULL**/
INSERT INTO tb_four_in_one([id],type,title,downURL,[desc],downLength,lyric,author,tryURL,tryLength)
SELECT
MAX(CASE WHEN x LIKE 'author%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS author,
MAX(CASE WHEN x LIKE 'desc%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS [desc],
MAX(CASE WHEN x LIKE 'downLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downLength,
MAX(CASE WHEN x LIKE 'downURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downURL,
MAX(CASE WHEN x LIKE 'id%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS id,
MAX(CASE WHEN x LIKE 'lyric%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS lyric,
MAX(CASE WHEN x LIKE 'title%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS title,
MAX(CASE WHEN x LIKE 'tryLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryLength,
MAX(CASE WHEN x LIKE 'tryURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryURL,
MAX(CASE WHEN x LIKE 'type%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS type
FROM #myTemp2
GROUP BY groupid; 这段代码后警告: 聚合或其它 SET 操作消除了空值。(所影响的行数为 1 行)
[id] [int] NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[title] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[downURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[desc] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[downLength] [int] NULL ,
[lyric] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[tryURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[tryLength] [int] NULL)
CREATE TABLE #tmp(x varchar(8000));
BULK INSERT #tmp
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR = '\r'
);DELETE #tmp WHERE x IS NULL;SELECT rowid=IDENTITY(int,1,1),* INTO #myTemp1 FROM #tmp;SELECT *,groupid=(SELECT MAX(rowid) FROM #myTemp1
WHERE rowid <= A.rowid
AND RTRIM(LTRIM(x)) LIKE '$[info%' ESCAPE '$')
INTO #myTemp2
FROM #myTemp1 AS A;INSERT INTO [dbo].[tb_four_in_one]
SELECT
MAX(CASE WHEN x LIKE 'id%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS id,
MAX(CASE WHEN x LIKE 'type%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS type,
MAX(CASE WHEN x LIKE 'title%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS title,
MAX(CASE WHEN x LIKE 'downURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downURL,
MAX(CASE WHEN x LIKE 'desc%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS [desc],
MAX(CASE WHEN x LIKE 'downLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS downLength,
MAX(CASE WHEN x LIKE 'lyric%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS lyric,
MAX(CASE WHEN x LIKE 'author%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS author,
MAX(CASE WHEN x LIKE 'tryURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryURL,
MAX(CASE WHEN x LIKE 'tryLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) END) AS tryLength
FROM #myTemp2
GROUP BY groupid;
SELECT * FROM [dbo].[tb_four_in_one]--删除临时表
DROP TABLE [dbo].[tb_four_in_one]
DROP TABLE #tmp,#myTemp1,#myTemp2;/*
id type title downURL desc downLength lyric author tryURL tryLength
39994 music 万世师表开场组曲 (纯音乐) D:/Res/res3//music/albums/79/399949975780.mp3 896234 陈浩峰 D:/Res/res3//music/albums/79/399949975780.mp3 896234
12003811 video 惹人眼球的美女模特 D:/Res/res4/video/4007/20090817113840674.3gp 韩国车展惹人眼球的美女模特 880636 NULL NULL NULL NULL
1502743 article 擒情贵公子 D:/Res/res1/txt/3005/20090330161228196.txt 殷尚恭是霸王 124111 NULL NULL NULL NULL
1008905 img 可爱 D:/Res/res3/img/2017/20090519111859198.gif 22756 NULL NULL NULL NULL
*/
(所影响的行数为 1 行)
(所影响的行数为 0 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)警告: 聚合或其它 SET 操作消除了空值。(所影响的行数为 1 行)
数据库里就只有一条数据: NULL NULL NULL NULL
27楼、29楼在sql2000下测试都没有问题。
警告: 聚合或其它 SET 操作消除了空值。(所影响的行数为 1 行)
[id] [int] NOT NULL ,
[type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[title] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[downURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[desc] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[downLength] [int] NULL ,
[lyric] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[tryURL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[tryLength] [int] NULL)
CREATE TABLE #tmp(x varchar(8000));
BULK INSERT #tmp
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR = '\r'
);DELETE #tmp WHERE x IS NULL;SELECT rowid=IDENTITY(int,1,1),* INTO #myTemp1 FROM #tmp;SELECT *,groupid=(SELECT MAX(rowid) FROM #myTemp1
WHERE rowid <= A.rowid
AND RTRIM(LTRIM(x)) LIKE '$[info%' ESCAPE '$')
INTO #myTemp2
FROM #myTemp1 AS A;INSERT INTO [dbo].[tb_four_in_one]
SELECT
MAX(CASE WHEN x LIKE 'id%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS id,
MAX(CASE WHEN x LIKE 'type%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS type,
MAX(CASE WHEN x LIKE 'title%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS title,
MAX(CASE WHEN x LIKE 'downURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS downURL,
MAX(CASE WHEN x LIKE 'desc%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS [desc],
MAX(CASE WHEN x LIKE 'downLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS downLength,
MAX(CASE WHEN x LIKE 'lyric%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS lyric,
MAX(CASE WHEN x LIKE 'author%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS author,
MAX(CASE WHEN x LIKE 'tryURL%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS tryURL,
MAX(CASE WHEN x LIKE 'tryLength%'
THEN RIGHT(x,CHARINDEX('=',REVERSE(x))-1) ELSE '' END) AS tryLength
FROM #myTemp2
GROUP BY groupid;
SELECT * FROM [dbo].[tb_four_in_one]--删除临时表
DROP TABLE [dbo].[tb_four_in_one]
DROP TABLE #tmp,#myTemp1,#myTemp2;
CREATE TABLE #tmp(x varchar(8000));
BULK INSERT #tmp
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR = '\r'
);DELETE #tmp WHERE x IS NULL;--执行到这里,把#tmp的记录显示下出来看看.SELECT * FROM #tmp;
pause
下面的保存为一个vbs文件, 比如tt.vbs, 位置要跟上一步的bat文件在同一个目录
Call mainSub main()
Dim fso, fs1 , sql_md, sqls
Dim fields , is_begin
Dim k, v
is_begin = False
fields = "[id],[type],[title],[downURL],[desc],[downLength],[lyric],[author],[tryURL],[tryLength]"
sql_md = "insert into [dbo].[tb_four_in_one](" & fields & ") values("
fields = split(fields,",")
For i=0 To UBound(fields) step 1
sql_md = sql_md & "@" & fields(i) & "@,"
Next
sql_md = Left(sql_md, Len(sql_md)-1) & ");"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fs1 = fso.OpenTextFile("c:\tt.txt") '要分析的文本数据文件
Set fs2 = fso.CreateTextFile("c:\tt.sql") '分析完成后输出的sql文件
Do While fs1.AtEndOfStream = False
s = fs1.ReadLine()
s = Trim(s)
s = replace(s,Chr(9),"")
If InStr(s,"[")=1 And InStr(s,"]")=Len(s) Then
If is_begin = False Then
'wscript.echo s
fs2.WriteLine "--" & s
is_begin = True
Else
For i=0 To UBound(fields) step 1
sqls = replace(sqls, "@" & fields(i) & "@", "NULL")
Next
'wscript.echo sqls
fs2.WriteLine sqls
'wscript.echo s
fs2.WriteLine "--" & s
End If
sqls = sql_md
ElseIf InStr(s,"=")>0 Then
k = Trim(Left(s, InStr(s,"=")-1))
v = Trim(Right(s, Len(s) - InStr(s,"=")))
If Len(v)<1 Then v = "NULL"
Select Case k
Case "id"
sqls = replace(sqls, "@[" & k & "]@", v)
Case "downLength"
sqls = replace(sqls, "@[" & k & "]@", v)
Case "tryLength"
sqls = replace(sqls, "@[" & k & "]@", v)
Case Else
If v<>"NULL" Then v="'" & replace(v,"'","''") & "'"
sqls = replace(sqls, "@[" & k & "]@", v)
End Select
End If
Loop
If InStr(sqls,"@[id]@")< 1 Then
For i=0 To UBound(fields) step 1
sqls = replace(sqls, "@" & fields(i) & "@", "NULL")
Next
fs2.WriteLine sqls
'wscript.echo sqls
End If
fs1.Close
fs2.Close
Set fs1 = Nothing
Set fs2 = Nothing
Set fso = Nothing
wscript.echo "Action completed!"
End Sub最后自己改一下vbs文件中要输入的txt文件和输出的sql文件具体路径, 把sql文件放到查询分析器里面执行就ok了。
后只有一条记录?
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR = '\r' --应该是我这里写错了..你试一下\n.如果不行.再试一下\r\n
);
不行 效果跟FIELDTERMINATOR='\r'是一样的
还是没改变什么,#mytemp2也还是只有1行数据,four_in_one 表也是个之前一样。。
在几台机测试过..正确..估计是RPWT..那你直接用39楼的吧.