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.

SET NOCOUNT ON

DECLARE @folder_path VARCHAR(200) = 'D:\temp'
DECLARE @current_file VARCHAR(100)
DECLARE @file_path VARCHAR(200)
DECLARE @sql NVARCHAR(4000)

DROP TABLE IF EXISTS #FilesList;
--IF OBJECT_ID('tempdb..#FilesList') IS NOT NULL
--DROP TABLE #FilesList

CREATE TABLE #FilesList (
       id INT IDENTITY(1,1)
      ,subdirectory nVARCHAR(512)
      ,depth INT
      ,isfile BIT
      ,isprocessed BIT
);

INSERT #FilesList (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @folder_path,0,1

UPDATE #FilesList set isprocessed = 0

WHILE EXISTS(SELECT TOP 1 subdirectory FROM #FilesList WHERE isprocessed = 0)
BEGIN
 SET @current_file = (SELECT TOP 1 subdirectory FROM #FilesList WHERE isprocessed = 0)
 SET @file_path = @folder_path + '\'SET NOCOUNT ON

DECLARE @folder_path VARCHAR(200) = 'D:\temp'
DECLARE @current_file VARCHAR(100)
DECLARE @file_path VARCHAR(200)
DECLARE @table_name varchar(100) = '[dbo].[GHCN]'
DECLARE @sql NVARCHAR(4000)

DROP TABLE IF EXISTS #FilesList;
--IF OBJECT_ID('tempdb..#FilesList') IS NOT NULL
--DROP TABLE #FilesList

CREATE TABLE #FilesList (
       id INT IDENTITY(1,1)
      ,subdirectory nVARCHAR(512)
      ,depth INT
      ,isfile BIT
   ,isprocessed BIT
);

INSERT #FilesList (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @folder_path,0,1

UPDATE #FilesList set isprocessed = 0 
WHILE EXISTS(SELECT TOP 1 subdirectory FROM #FilesList WHERE isprocessed = 0)
BEGIN
 SET @current_file = (SELECT TOP 1 subdirectory FROM #FilesList WHERE isprocessed = 0)
 SET @file_path = @folder_path + '\' + @current_file
 SET @sql = 
  N'BULK INSERT '+ @table_name + ' ' +
  'FROM ''' + @file_path+ '''' +
  ' WITH(
   FIRSTROW = 2,
   ROWTERMINATOR = ''0x0a'',
   FIELDTERMINATOR = '','',
   TABLOCK
  );'
 UPDATE #FilesList SET isprocessed = 1 WHERE subdirectory = @current_file
 EXEC (@sql)
END + @current_file
 SET @sql = 
  N'BULK INSERT [dbo].[ghcn]
  FROM ''' + @file_path+ '''' +
  ' WITH(
   FIRSTROW = 2,
   ROWTERMINATOR = ''0x0a'',
   FIELDTERMINATOR = '','',
   TABLOCK
  );'
 UPDATE #FilesList SET isprocessed = 1 WHERE subdirectory = @current_file
 EXEC (@sql)
END