仓酷云

标题: MSSQL教程之利用T-SQL导进多个文件数据到SQL Serve... [打印本页]

作者: 变相怪杰 时间: 2015-1-16 22:32
标题: MSSQL教程之利用T-SQL导进多个文件数据到SQL Serve...
这类操作的执行过程是，1)按照新的表定义建立一个临时表tmpa，2)将原表数据拷贝到临时表，3)将原始表改名tmpb，4)将tmpa改名为原表名，5)将tmpb删除。server|数据
利用T-SQL导进多个文件数据到SQLServer中

在我们的事情中，常常必要一连输出多个文件的数据到SQLServer的表中，偶然必要从不异大概分歧的目次中，同时将文件中的数据倒进。在这篇文章中，我们将会商怎样同时把一个目次中的文件的数据倒进到SQLServer中。

实验情况
我们先创立全部实验的情况。创立文件目次“C:MyImport”，和三个文件a.csv、b.csv和c.csv，文件内容以下。同时，在SQLServer中创立一个表用来寄存导进的数据。
C:MyImporta.csv
1,MAK,A9411792711,3400.25
2,Claire,A9411452711,24000.33
3,Sam,A5611792711,1200.34
C:MyImport.csv
11,Rubon,9671792711,400.14
22,Mike,9418952711,4000.56
39,Hsu,75611792511,1230.00
C:MyImportc.csv
69,Lucy,8411992710,305.11
45,Grace,3413452713,246.52
33,Saint,5461795716,1278.70
CreateDatabaseBank
Go
UseBank
go
CreatetableAccount
(
[ID]int,NameVarchar(100),
AccountNovarchar(100),Balancemoney
)
Go
Createtablelogtable
(
idintidentity(1,1),
Queryvarchar(1000),
Importeddatedatetimedefaultgetdate()
)

办法1:XP_CMDSHELL和BULKINSERT
这个办法利用xp_cmdshell和BulkInsert的SQL命令把一个目次中的文件倒进到SQLServer的表中。

创立存储历程

在数据库中发生这个存储历程，这个存储历程有三个参数：文件路径，文件扩大名和数据库的表名。

Createprocedureusp_ImportMultipleFiles@filepathvarchar(500),
@patternvarchar(100),@TableNamevarchar(128)
as
setquoted_identifieroff
declare@queryvarchar(1000)
declare@max1int
declare@count1int
Declare@filenamevarchar(100)
set@count1=0
createtable#x(namevarchar(200))
set@query=master.dbo.xp_cmdshell"dir+@filepath+@pattern+/b"
insert#xexec(@query)
deletefrom#xwherenameisNULL
selectidentity(int,1,1)asID,nameinto#yfrom#x
droptable#x
set@max1=(selectmax(ID)from#y)
--print@max1
--print@count1
While@count1<=@max1
begin
set@count1=@count1+1
set@filename=(selectnamefrom#ywhere[id]=@count1)
set@Query=BULKINSERT+@Tablename+FROM"+@Filepath+@Filename+"
WITH(FIELDTERMINATOR=",",ROWTERMINATOR="
")
--print@query
exec(@query)
insertintologtable(query)select@query
end

droptable#y

实行
实行下面的存储历程，参数以下：
例1：输出一切的c:myimport目次下的.csv文件到Account表中
Execusp_ImportMultipleFilesc:myimport,*.csv,Account

例2：输出一切的c:myimport目次下的文件到Account表中
Execusp_ImportMultipleFilesc:myimport,*.*,Account

办法2:XP_CMDSHELL和BCP公用程序
这个办法利用"xp_cmdshell"和"BCP.exe"倒进一个目次下的文件到SQLServer的表中。这个存储历程以服务器名、数据库名、文件路径、文件扩大名和数据库表名作为参数。
注重：确信你运转SQLServerAgent的帐户具有会见你输出的文件夹和服务器的权限。

创立存储历程：
setquoted_identifieroff
go
Createprocedureusp_ImportMultipleFilesBCP@servernamevarchar(128),
@DatabaseNamevarchar(128),@filepathvarchar(500),@patternvarchar(100),
@TableNamevarchar(128)
as
declare@queryvarchar(1000)
declare@max1int
declare@count1int
Declare@filenamevarchar(100)
set@count1=0
createtable#x(namevarchar(200))
set@query=master.dbo.xp_cmdshell"dir+@filepath+@pattern+/b"
insert#xexec(@query)
deletefrom#xwherenameisNULL
selectidentity(int,1,1)asID,nameinto#yfrom#x
droptable#x
set@max1=(selectmax(ID)from#y)
--print@max1
--print@count1
--select*from#y
While@count1<=@max1
begin
set@count1=@count1+1
set@filename=(selectnamefrom#ywhere[id]=@count1)
set@Query=bcp"+@databasename+.dbo.+@Tablename+"
in"+@Filepath+@Filename+"-S+@servername+-T-c-r
-t,
set@Query=MASTER.DBO.xp_cmdshell+""+@query+""
--print@query
EXEC(@query)
insertintologtable(query)select@query
end

droptable#y

实行
实行下面的存储历程，参数以下：
例1：输出一切的c:myimport目次下的.csv文件到Account表中
Execusp_ImportMultipleFilesBCPSQL,Bank,c:Myimport,*.csv,Account

例2：输出一切的c:myimport目次下的文件到Account表中
Execusp_ImportMultipleFilesBCPSQL,Bank,c:Myimport,*.*,Account

了局
不论你利用办法一仍是办法二，数据将会被导进到SQLServer的表中。以下所示：

1

MAK

A9411792711

3400.25

2

Claire

A9411452711

24000.33

3

Sam

A5611792711

1200.34

11

Rubon

9671792711

400.14

22

Mike

9418952711

4000.56

39

Hsu

75611792511

1230

69

Lucy

8411992710

305.11

45

Grace

3413452713

246.52

33

Saint

5461795716

1278.7

利用办法一发生的日记纪录以下表：

1

BULKINSERTAccountFROM"c:myimporta.csv"WITH(FIELDTERMINATOR=",",ROWTERMINATOR="
")

3/1/042:15

2

BULKINSERTAccountFROM"c:myimport.csv"WITH(FIELDTERMINATOR=",",ROWTERMINATOR="
")

3/1/042:15

3

BULKINSERTAccountFROM"c:myimportc.csv"WITH(FIELDTERMINATOR=",",ROWTERMINATOR="
")

3/1/042:15

利用办法二发生的日记纪录以下表：

1

MASTER.DBO.xp_cmdshellbcp"Bank.dbo.Account"in"c:Myimporta.csv"-SSQL-T-c-r
-t,

3/1/042:18

2

MASTER.DBO.xp_cmdshellbcp"Bank.dbo.Account"in"c:Myimport.csv"-SSQL-T-c-r
-t,

3/1/042:18

3

MASTER.DBO.xp_cmdshellbcp"Bank.dbo.Account"in"c:Myimportc.csv"-SSQL-T-c-r
-t,

3/1/042:18

总结
像后面提到的一样，这篇文章的目标是告知你怎样从一个文件夹中导进多个文件到SQLServer的表中。这些存储历程能够进一步加强，能够用xp_getfiledetails来判别转进的文件的最初修正工夫来断定文件的局限。这些存储历程也能够创立为准时实行的功课。在导进完成今后，你能够用rename或move命令来重定名大概移到分歧的目次中。

参考:

利用DTS导进多个文件数据到SQLServer中
为多种编程语言提供了API。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

作者: 第二个灵魂 时间: 2015-1-19 16:17
如果，某一版本可以提供强大的并发响应，但是没有Oracle的相应版本稳定，或者价格较贵，那么，它就是不适合的。

作者: 分手快乐 时间: 2015-1-26 15:03
可以动态传入参数，省却了动态SQL的拼写。

作者: 若相依 时间: 2015-2-4 17:05
相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

作者: 小魔女 时间: 2015-2-10 04:52
也可谈一下你是怎么优化存储过程的?

作者: 灵魂腐蚀 时间: 2015-2-28 20:35
从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

作者: 柔情似水 时间: 2015-3-10 07:54
很多书籍啊，不过个人认为看书太慢，还不如自己学。多做实际的东西，就会遇到很多问题，网上搜下解决问题。不断重复这个过程，在配合sql的F1功能。

作者: 再现理想 时间: 2015-3-17 05:42
我们学到了什么？思考问题的时候从表的角度来思考问

作者: 小妖女 时间: 2015-3-23 22:27
从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

欢迎光临仓酷云 (http://www.ckuyun.com/)