0

I'm new to SQL with a background in OOL type programming.

I've created the following query which successfully imports .CSV files into a table on SQL Management Studio. How would I go about importing multiple files? This would be quite straightforward using an OOL language, although I heard you have to read directories using cmd?

The working code is as following:

--Cihans Import for Holdings-- 
INSERT INTO Holdings1
SELECT * FROM OPENROWSET
('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)}
;DBQ=C:\Share\DataUploads\FundHoldings;','SELECT * FROM holdings.csv')

How would I go about looping/reading all files in a directory and importing data? We are given over 120 sheets a month, and I would like to import using the above. Or if anyone can recommend an alternative to this?

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
Coder1994UK
  • 28
  • 3
  • 9

1 Answers1

0

Do you have access to SSIS? (SQL Server Integration Services)?

If so you can set up an ETL task using this to import all files in a folder using a FOREACH LOOP CONTAINER which contains your data flow task & file system task?

Edit - Response to comment - The Solution in this thread should enable you to do what you need using just T-SQL: loop through files in folder

Basically - load all the files into a temptable, create a while loop, passing in a new filename from the folder each execution, then perform your data manipulation, then pass in the next filename until all are complete.

Community
  • 1
  • 1
Uberzen1
  • 415
  • 6
  • 18
  • That's a really good idea, and I totally agree with you. The lead mathematical programmer is not too keen on SSIS since he prefers less add-ons etc. Therefore, I'm left in a position where I don't know how I would do this; the only option being TSQL that would do it for me. – Coder1994UK Sep 17 '15 at 15:56