0

I have a folder with dozens of files. The files all have a .txt file extension, even though they are comma deliminated.

I would like to create a SQL Server stored procedure that does the following, when given a directory path and a specific database, creates a new table in the database for each file. The table name will be the file name, and the table data will be the data within the file.

For example, file "xxABC.txt" has 500 records in comma separated format (excluding header). The procedure will create table xxABC and then insert 500 rows into the table.

I've found how to list files in a directory using SQL Server (How to list files inside a folder with SQL Server), but still trying to figure out how to create tables based on files.

Community
  • 1
  • 1
user3150002
  • 141
  • 1
  • 8
  • For anyone who may come by this question in the future, and have the same issue: using BULK INSERT works. You just have to make sure that you set up the table and file path beforehand, which can be done. I am writing my script now, but will post mky example later. – user3150002 Jun 18 '14 at 19:56
  • And also for what it's worth to future readers, I would have approached this with an SSIS package. The directory path can be passed to an SSIS variable, and can be used in a ForEachFile task to loop through all files, and execute a SQL command for each (presumably a proc that creates the table and then populates it) – Tab Alleman Jun 18 '14 at 20:24

0 Answers0