Import multiple Files to SQL Server using T-SQL

In a typical IT environment, it
is often necessary to import flat files to SQL Server tables. Sometimes it is
necessary to import many files at the same time, from the same or different
folders. In this article, I am going to discuss how to import all of the files
from a particular folder to SQL Server.

Simulation

Let’s simulate the whole scenario for importing multiple
files. Let’s create a folder C:MyImport and create 3 files, a.csv, b.csv and c.csv
with the content displayed below. Also, create a table in SQL Server that can
hold the imported data.

Files

C:MyImporta.csv

1, MAK, A9411792711, 3400.25

2, Claire, A9411452711, 24000.33

3, Sam, A5611792711, 1200.34

C:MyImportb.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

Table

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())

Go

Method 1: XP_CMDSHELL and BULK INSERT

This method uses "xp_cmdshell"
and "BULK Insert" SQL commands to import all of the files from a
folder to a SQL Server table.

Create procedure

Create the below procedure in the database. This procedure
accepts Filepath, pattern (wildcard) and table name as parameters.

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

Execute the Procedure

Execute the above procedure by passing parameters as shown
below.

Example 1: To import all .csv files from the folder
c:myimport to a table Account

Exec usp_ImportMultipleFiles 'c:myimport', '*.csv', 'Account'

Example 2: To import all files from the folder
c:myimport to a table Account

Exec usp_ImportMultipleFiles 'c:myimport', '*.*', 'Account'

Method 2: XP_CMDSHELL and BCP Utility

This method uses "xp_cmdshell" and "BCP.exe"
to import all of the files from a folder to a SQL Server table. This procedure
accepts Server name, Database Name, Filepath, pattern (wildcard) and table name
as parameters.

Note: Make sure SQL Server
Agent is running under an account that has enough access to all the folders and
server names that you are passing as parameters.

Create procedure

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 -rn -t,’

set @Query = ‘MASTER.DBO.xp_cmdshell ‘+ “‘”+  @query +”‘”

–print @query

EXEC ( @query)

insert into logtable (query) select @query

end

drop table #y

Execute the Procedure

Execute the above procedure by passing the parameters shown
below.

Example 1: To import all .csv files from folder
c:myimport to a table Account

Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:Myimport','*.csv','Account'

Example 2: To import all files from the folder
c:myimport to a table Account

Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:Myimport','*.*','Account'

Results

Either by following method 1 or by method 2, the data
would be imported to the SQL Server table as shown below.

The log table when Method 1 is used will look like this:

The log table when Method 2 is used will look like this:

Conclusion:

As mentioned before, the intention of this article is to
guide you in how to import multiple files from a folder to a SQL Server table.
These procedures can be enhanced in such a way that it accepts only the file
names that are modified on a particular date by using the un-documented extended
stored procedure xp_getfiledetails. These procedures can also be scheduled as
jobs. After importing all of the files from the folder, you can archive all the
files by using the "rename" or "move" command to rename the
files and move them to different folders.

»


See All Articles by Columnist MAK