How to import file contents into table column as data

Question

A project I'm working on at work involves modifying one of the subsystems to store/pull data that is currently stored in files into the database. Each of the files is a single, sometimes-large, chunk of custom (xml-based) script generated by another custom tool.

Conceptually, I'm looking for an easy way to do something like:

For Each file in folder_and_subfolders
    INSERT INTO table
    (script_name, version_num, script )
    VALUES
    ({file_name}, 1, {file_contents})
    ;
Next

Preferably on an entire directory tree at once.

If there's no easy way to do this via T-SQL, I can write a utility to do the job, but I'd prefer something that didn't require having to write another custom tool that will only be used once.

SSIS would easily be able to achieve this using a For Each Loop Container; and is part of SQL Server Standard+. T-SQL (on it's own), is going to likely be far harder to use to achieve this. — Thom A, Jan 28 '19 at 16:35
Yeah, no. You can certainly dir and read files by enabling `xp_cmdshell` and getting jiggy with that, but then you're also looking at some nasty cursor loops to process the results of those commands. Invert the control flow if you can and do this from the client side -- you don't need to write separate tools for this; PowerShell should be available on any recent Windows server, including the SQL Server itself. (In my humble, personal opinion and experience: avoid SSIS like the plague, although I'll admit it's an option in this case.) — Jeroen Mostert, Jan 28 '19 at 16:37
Possible duplicate of [Import Multiple CSV Files to SQL Server from a Folder](https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder) — EzLo, Jan 28 '19 at 16:47
@EzLo, I don't think this is a duplicate. This isn't files containing bulk data to insert into multiple rows of a table. This is multiple files, each containing a single piece of data — Theo Brinkman, Jan 28 '19 at 16:59
Does [this help](https://stackoverflow.com/a/3152516/685760) for at least how to reference the file contents? You'd still need to work out how to iterate over all the files you want, but maybe it could be a combination of sqlcmd and a query that is referenced in that answer alongside a batch or powershell script. — Mr Moose, Jan 28 '19 at 17:12
@MrMoose, it appears that that might handle the {file_contents} part of the question. Thanks. — Theo Brinkman, Jan 28 '19 at 18:02

score 0 · Answer 1 · answered Jan 28 '19 at 18:41

So, I don't have SQL Server installed and therefore can't test this, but if you are looking for a simple batch file that could do what you're after, I'd suggest something like the following might well help;

@echo off

SET xmldir=./myxmlfiles/live/here/

echo --- Processing files

for %%f in ("%xmldir%*.xml") do (echo Running %%f.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -v filename="%xmldir%%%f" -i ProcessFile.sql)

I'm not sure how much you know about sqlcmd, but it is a command line tool that is generally provided by SQL Server. It will allow you to run SQL commands, or in the case above, run a script which is indicated by the -i parameter. I am assuming that you'd place your SQL statement in there to perform your additions to the table.

The other parameters to sqlcmd are described below;

-I sets QUOTED_IDENTIFIER on (you may or may not need this. I did for an earlier issue I faced with sqlcmd and QUOTED_IDENTIFIER)

-U sets the database username

-P sets the database password

-S sets the database server

-d sets the database to connect to

-v is the interesting one here as it lets you pass parameters to your script. Note that on the MSDN page describing this, it states that if your path or filename contains spaces, then you'll need to enclose it in quotes, so check that out. Basically though, you'd be able to refer to the parameter inside your sql script (ProcessFile.sql) like INSERT INTO mytable (file_name) VALUES ('$(filename)')

You'd have to use the logic described in the answer from my previous comment to ensure

How to import file contents into table column as data

1 Answers1