Import multiple file data to SQL Server using a T-SQL

In our work, we often need to continuously input data from multiple files to the SQL Server table. Sometimes, we need to import the data in the file from the same or different directories. In this article, we will discuss how to import the file data in a directory to SQL server at the same time. Test Environment
We will first create the entire test environment. Create the file directory "C: \ myimport”" and the three files a.csv20. B .csvand c.csv. The file content is as follows. At the same time, create a table in SQL Server to store the imported data.
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,400 0.56
39, Hsu, 75611792511,123 0.00
C: \ myimport \ c.csv
69. Lucy, 8411992710,305.11
45, Grace, 3413452713,246.52
33, Saint, 5461795716,127 8.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 ()
)
Method 1: xp_mongoshell AND BULK INSERT
This method uses the SQL commands xp_cmdshell and bulk insert to import files in a directory to the SQL Server table.

Create a stored procedure

This stored procedure is generated in the database and has three parameters: file path, file extension, and database table name.

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_mongoshell "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

Run
Run the preceding stored procedure with the following parameters:
Example 1: Enter the. CSV file in the C: \ myimportdirectory to the account table.
Exec usp_importmultiplefiles 'C: \ myimport \ ',' *. csv', 'account'

Example 2: Enter all files in the C: \ myimport directory to the account table.
Exec usp_importmultiplefiles 'C: \ myimport \ ',' *. * ', 'account'

Method 2: xp_cmdshell and BCP
This method uses "xp_cmdshell" and "bcp.exe" to import files in a directory to the SQL Server table. This stored procedure takes the server name, database name, file path, file extension, and database table name as parameters.
Note: Make sure that your account running SQL Server Agent has the permission to access the folders and servers you entered.

Create a stored 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_mongoshell "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_mongoshell '+ "'" + @ query + "'"
-- Print @ Query
Exec (@ query)
Insert into logtable (query) Select @ Query
End

Drop table # Y

Create a batch file
Create a batch file c: \ myimport \ import. BAT as follows.
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

Execute batch files
After the batch processing file is executed, it will upload a dirlist.txt file in the C: \ myimp directory. This file will contain all files expanded to csvunder c: \ myimport, which will be transmitted to dtsrun.exe as well as other files. In this example, the content of dirlist.txt is as follows:
C: \ myimport \ dirlist.txt
Acsv
B .csv
C.csv
Note: In the batch file, set the servername, filename, and databasename parameters according to your own situation.

Run
Run the preceding stored procedure with the following parameters:
Example 1: Enter the. CSV file in the C: \ myimportdirectory to the account table.
Exec usp_importmultiplefilesbcp 'SQL', 'bank', 'c: \ myimport \ ',' *. csv', 'account'

Example 2: Enter all files in the C: \ myimport directory to the account table.
Exec usp_importmultiplefilesbcp 'SQL', 'bank', 'c: \ myimport \ ',' *. * ', 'account'

Result
Whether you use method 1 or method 2, the data will be imported to the SQL Server table. As follows:

1
Mak
A9411792711
3400.25
 
2
Clare
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
 

Use method 1 to generate the following log records:

 

1
Bulk insert account from "C: \ myimport \ a.csv" with (fieldterminator = ",", rowterminator = "\ n ")
3/1/04
 
2
Bulk insert account from "C: \ myimport \ B .csv" with (fieldterminator = ",", rowterminator = "\ n ")
3/1/04
 
3
Bulk insert account from "C: \ myimport \ c.csv" with (fieldterminator = ",", rowterminator = "\ n ")
3/1/04
 

Use method 2 to generate the following log records:

1
Master. DBO. xp_mongoshell 'bcp "bank. DBO. Account" in "C: \ myimport \ a.csv"-ssql-t-c-r \ n-t ,'
3/1/04
 
2
Master. DBO. xp_mongoshell 'bcp "bank. DBO. Account" in "C: \ myimport \ B .csv"-ssql-t-c-r \ n-t ,'
3/1/04
 
3
Master. DBO. xp_mongoshell 'bcp "bank. DBO. Account" in "C: \ myimport \ c.csv"-ssql-t-c-r \ n-t ,'
3/1/04
 

Summary
As mentioned above, this article aims to show you how to import multiple files from a folder to the SQL Server table. These stored procedures can be further enhanced. You can use xp_getfiledetails to determine the last modification time of the transferred file to determine the file range. These stored procedures can also be created as scheduled jobs. After the import is complete, you can use the rename or move command to rename or move to a different directory.

If you store the DTS package in SQL Server, write the batch file as follows:
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 "-s" SQL "-n" myimportdts "-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

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/08/11/1737931.aspx