BULK INSERT — dynamic SQL

Sometimes you have to import many CSV files into a database. You can do it using SSIS for instance. But there is a more convenient and flexible way to realize it.

To import one file you can use BULK INSERT.

BULK INSERT [dbo].[GHCN]
FROM ‘D:\New\2020.csv’
WITH(
 FIRSTROW = 2,
 ROWTERMINATOR = ‘0x0a’,
 FIELDTERMINATOR = ‘,’,
 TABLOCK
)

You cannot provide a file and a table name through a variable. So that, we can use dynamic SQL.

The entire code bellow. Everything is pretty simple. So, I describe the most crucial points.

x-_dirtree — creates a list of files in a specified folder.

EXEC master.sys.xp_dirtree @folder_path,0,1

The loop works until there are records with “isprocessed = 1” values in the table.

WHILE EXISTS(
    SELECT TOP 1 subdirectory 
    FROM #FilesList 
    WHERE isprocessed = 0
)

At the end, dynamic query is created and executed for every file.