Import multiple file data into SQL Server using T-SQL
In our work, you often need to enter the data of multiple files into the table of SQL Server, sometimes you need to pour in the file in the same or different directories. In this article, we will discuss how to pour data from files in a directory into SQL Server.
Test Environment We first create the environment of the entire trial. Create a file directory "C: / myimport", and three file a.csv, b.csv and c.csv, the file content is as follows. At the same time, create a table in SQL Server to store 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, 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 Iden ", query varchar (1000), IMPORTEDDATE DATETIME DEFAULT GETDATE ())
Method 1: XP_cmdshell and bulk INSERT This method uses XP_cmdshell and Bulk INSERT's SQL command to pour the files in a directory into the table of SQL Server. Creating a stored procedure generates this stored procedure in the database, this stored procedure has three parameters: file path, file extension, and table name of the database. Create procedure usp_ImportMultipleFiles @filepath varchar (500), @pattern varchar (100), @TableName varchar (128) asset quoted_identifier offdeclare @query varchar (1000) declare @ max1 intdeclare @ count1 intDeclare @filename varchar (100) set @ count1 = 0create 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 nullselect Identity (int, 1, 1) AS ID, Name Into #y from #x Drop Table #xset @ max1 = (Select Max (ID) from From #Y) - Print @ Max1 - Print @ count1while @ count1 <= @ max1beginset @ count1 = @ count1 1set @filename = (select name from #y where [id] = @ count1) set @Query = 'BULK INSERT' @Tablename 'fROM "' @ Filepath @ Filename '" WITH ( Fieldterminator = ",", ROWTERMINATOR = "/ N") '- print @queryexec (@query) INSERT INTO LOGTABLE (Query) SELECT @queryEndDrop Table #y Executes the above stored procedure, parameters are as follows: Example 1: Enter all The .csv file under the c: / myimport directory in the Account table, EXEC USP_IMPORTMULTIPLEFILES 'C: / MyImportMultiplefiles' C: / MyImport /', '* .csv', 'Account' Example 2: Enter all C: / MyImport directory files to AC Exec USP_IMPORTMULTIPLEFILES 'C: / MyImportMultiplefiles' C: / MyImportMultiplefiles 'C: / MyImportMultiplefiles' C: / MyImportMultiplefiles 'C: / MyImport /', 'Add'
Method 2: XP_cmdshell and BCP Utilities This method uses "XP_cmdshell" and "BCP.exe" poured into a directory to the table of SQL Server. This stored procedure is used as a parameter in server name, database name, file path, file extension, and database table name. Note: Confident that you run the SQL Server Agent account has access to folders you entered and server. Create a stored procedure: set quoted_identifier offgoCreate procedure usp_ImportMultipleFilesBCP @servername varchar (128), @ DatabaseName varchar (128), @filepath varchar (500), @pattern varchar (100), @TableName varchar (128) asdeclare @query varchar (1000) declare @ max1 intdeclare @ count1 intDeclare @filename varchar (100) set @ count1 = 0create 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 nullselect Identity (int, 1, 1) AS ID, Name Into #y from #x drop table #xset @ max1 = (Select Max (ID) from from #Y) - Print @ max1 - print @ count1 - select * from #ywhile @ count1 <= @ max1beginset @ count1 = @ count1 1set @FileName = (select name from #y where [id] = @ count1) Set @query = 'bcp "' @databaseename '. dbo.' @tablename '" in "' @ filepath @ filename '" -t -c-r / n -t " , 'set @query =' master.dbo.xp_cmdshell ' "" @query "'" - print @queryexec (@query) Insert Into logtable (query) Select @queryEnddrop Table #y
Perform the above stored procedure, the parameters are as follows: Example 1: Enter all the .csv files under all C: / myimport directory to the AccountMultMultipleFilesbcp 'SQL', 'Bank', 'C: / MyImport /', '* .csv ',' Account 'Example 2: Enter all the files in all C: / myimport directory to the Account table exec USP_IMPORTMULTIPLEFILESBCP' SQL ',' Bank ',' C: / MyImport / ',' *.* ',' Account 'Results No matter what you use is also a method, the data will be imported into the table of SQL Server. As follows:
1
MAK
A9411792711
3400.25
2
Claire
A9411452711
24000.33
3
Sam
A5611792711
1200.34
11
Rubon
9671792711
400.14
twenty two
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 records generated by the method of use are as follows:
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/CSV" with (FIELDTERMINATOR = ",", Rowterminator = "/ n")
3/1/04
2:15
The log records generated by the use method is as follows:
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/csv" -ssql -t -c -r / n -t,'
3/1/04
2:18
Summary As mentioned earlier, the purpose of this article is to tell you how to import multiple files into the table of SQL Server from a folder. These stored procedures can be further enhanced, and the last modification time of the transfer file can be used to determine the scope of the file with the last modification time of the transfer file. These stored procedures can also be created as a job that is performed at a timed. After the import is complete, you can rename or move to a different directory with a rename or move command. Reference: Import multiple file data into SQL Server using DTS
