Hello VeliJussiRaitila-8710 and thank you for your question.
To my understanding OPENROWSET can process multiple files from multiple folders.
Suppose we have files stored in path like mydata/year/month/day/file
:
mydata/2020/02/03/file1.csv
mydata/2020/02/03/file2.csv
mydata/2020/02/15/file1.csv
mydata/2020/02/15/file2.csv
mydata/2020/03/20/file1.csv
If you want to read all the above files, it would look like
SELECT *
FROM OPENROWSET(
BULK 'mydata/2020/*/*/file*.csv' ,
DATA_SOURCE = 'myExternalDataSource' ,
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
If I wanted to select data from all months, but only days after 14, it could look something like
SELECT r.filepath(1) as [MONTH], r.filepath(2) as [DAY], data
FROM OPENROWSET(
BULK 'mydata/2020/*/*/file*.csv' ,
DATA_SOURCE = 'myExternalDataSource' ,
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
AS [r]
WHERE r.filepath(2) > '14'
References:
Use filename and filepath functions to target specific partitions
Use file metadata in queries
Query folders and multiple files
Please let me know if this helps, or if I misunderstood your ask.
Thanks,
Martin