Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 16, 2004

Import multiple Files to SQL Server using T-SQL

By Muthusamy Anantha Kumar aka The MAK

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:\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

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 -r\n -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.

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

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

1

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

3/1/04 2:15

2

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

3/1/04 2:15

3

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

3/1/04 2:15

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

1

MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\a.csv" -SSQL -T -c -r\n -t,'

3/1/04 2:18

2

MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\b.csv" -SSQL -T -c -r\n -t,'

3/1/04 2:18

3

MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\c.csv" -SSQL -T -c -r\n -t,'

3/1/04 2:18

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date