使用T-SQL导入多个文件数据到SQL Server中_10多张exex表l数据一次导入 sql server-CSDN博客

本文链接：https://blog.csdn.net/neekerss/article/details/3983815

创建文件目录“C:/MyImport”，和三个文件a.csv、b.csv和c.csv，文件内容如下。同时，在SQL Server中创建一个表用来存放导入的数据。
        C:/MyImport/a.csv
        1, MAK, A9411792711, 3400.25
        2, Claire, A9411452711, 24000.33
        3, Sam, A5611792711, 1200.34
        C:/MyImport/b.csv
        11, Rubon, 9671792711, 400.14
        22, Mike, 9418952711, 4000.56
        39, Hsu, 75611792511, 1230.00
        C:/MyImport/c.csv
        69, Lucy, 8411992710, 305.11
        45, Grace, 3413452713, 246.52
        33, Saint, 5461795716, 1278.70
        Create Database Bank
        Go
        Use Bank
        go
        Create table Account
        (
            [ID] int, Name Varchar(100),
            AccountNo varchar(100), Balance money
        )
        Go
        Create table logtable
        (
            id int identity(1,1),
            Query varchar(1000),
           Importeddate datetime default getdate()
        )

    方法 1: XP_CMDSHELL 和BULK INSERT
    这个方法使用xp_cmdshell和Bulk Insert的SQL命令把一个目录中的文件倒入到SQL Server的表中。

创建存储过程

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

Create procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "/n")'
--print @query
exec (@query)
insert into logtable (query) select @query
end

drop table #y

执行
执行上面的存储过程，参数如下：
例1：输入所有的c:/myimport目录下的.csv文件到Account表中
Exec usp_ImportMultipleFiles 'c:/myimport/', '*.csv', 'Account'

例2：输入所有的c:/myimport目录下的文件到Account表中
Exec usp_ImportMultipleFiles 'c:/myimport/', '*.*', 'Account'

    方法 2: XP_CMDSHELL 和 BCP 公用程序
    这个方法使用 "xp_cmdshell" 和 "BCP.exe" 倒入一个目录下的文件到SQL Server的表中。这个存储过程以服务器名、数据库名、文件路径、文件扩展名和数据库表名作为参数。
    注意：确信你运行SQL Server Agent的帐户具有访问你输入的文件夹和服务器的权限。

创建存储过程：
set quoted_identifier off
go
Create procedure usp_ImportMultipleFilesBCP @servername varchar(128),
@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
--select * from #y
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='bcp "'+ @databasename+'.dbo.'+@Tablename + '"
in "'+ @Filepath+@Filename+'" -S' + @servername + ' -T -c -r/n -t,'
set @Query = 'MASTER.DBO.xp_cmdshell '+ "'"+ @query +"'"
--print @query
EXEC ( @query)
insert into logtable (query) select @query
end

drop table #y

创建批处理文件
　如下所示创建批处理文件C:/MyImport/Import.bat。
　　　　REM Type: Batch File
　　　　REM Created by: Digjim
　　　　REM Import all csv files to SQL Server using DTS

　　　　REM Export DIR listing to C:/MyImport/Dirlist.txt
　　　　dir c:/MyImport/*.csv /b > C:/MyImport/Dirlist.txt

　　　　REM Execute DTS package for every file name in the Dirlist.txt

　　　　for /f "tokens=1,2,3" %%i in (C:/MyImport/Dirlist.txt) do "C:/Program Files/Microsoft SQL Server/80/Tools/Binn/DTSrun.exe" -F "c:/myimport/myimportdts1.dts" -U importuser -P import –A "ServerName"="DIGJIM" -A "FileName"="c:/myimport/%%i" -A "DatabaseName"="Bank"

　　　　REM Rename all the files with "old" as suffix
　　　　ren c:/myimport/*.csv *.oldcsv

　　执行批处理文件
　　当批处理文件被执行以后，他会在C:/MyImport目录下产生一个DirList.txt的文件，这个文件会包含所有C:/MyImport下扩展名为CSV的文件，这些文件名会和其他必须的参数一气传输给DTSrun.exe。在这个例子里，DirList.txt的内容如下：
　　　　C:/MyImport/DirList.txt
　　　　acsv
　　　　b.csv
　　　　c.csv
　　注意，在批处理文件中，根据你自己的情况设置ServerName，FileName和DatabaseName参数。

执行
执行上面的存储过程，参数如下：
例1：输入所有的c:/myimport目录下的.csv文件到Account表中
Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:/Myimport/','*.csv','Account'

例2：输入所有的c:/myimport目录下的文件到Account表中
Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:/Myimport/','*.*','Account'

结果
不管你使用方法一还是方法二，数据将会被导入到SQL Server的表中。如下所示：

MAK

A9411792711

3400.25

Claire

A9411452711

24000.33

Sam

A5611792711

1200.34

Rubon

9671792711

400.14

Mike

9418952711

4000.56

Hsu

75611792511

1230

Lucy

8411992710

305.11

Grace

3413452713

246.52

Saint

5461795716

1278.7

使用方法一产生的日志记录如下表：

BULK INSERT Account FROM "c:/myimport/a.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "/n")

3/1/04