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]
FIRSTROW = 2,
ROWTERMINATOR = ‘0x0a’,
FIELDTERMINATOR = ‘,’,
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.
SELECT TOP 1 subdirectory
WHERE isprocessed = 0
At the end, dynamic query is created and executed for every file.