Import multiple files into a table

I have a directory full of files. They're SQL scripts, but I don't want to run them. I want to import the contents of the script files into a table in my database to be run at a later time. Each file would have its own row in the table.

I'm currently working with Powershell, but if there's another easier method that doesn't use third party software then I would be open to that as well.

Originally I started to write code that would loop through the files, use StreamRea开发者_StackOverflow社区der to get the contents, then build an insert statement to execute. I realized though that there might be big problems with embedded quotes in the files, etc.

Thanks for any advice that you can give!

---

Can you remove the embedded quotes and mark them as logical section to-be replaced by either by String.Replace(old_str,new_str) or tsql REPLACE in your logic ? Below sample shows <condition_string> marker so you know you have take special care of it.

declare @finalQuery nvarchar(1000)

SET @finalQuery = 
(SELECT REPLACE ((
   SELECT REPLACE ((
      SELECT  REPLACE('Select <column_names> from <table_name> where 
                              <condition_string>' COLLATE Latin1_General_BIN,
      '<column_names>', 
      'ID,[name]' )),
   '<table_name>',
   '[MyTable]')),

'<condition_string>',
'[name] like ''%John%'''));

EXEC(@finalQuery);

GO

---

Normally when you're working with files in a database, you either want to reference them rather than importing the full contents (so you just store a path to the file on a physical volume) or you would store them as BLOBs (Binary Large Objects). If doing the latter, you can zip them too.

The downside of these techniques is that you can't just search them with a SQL query, but you don't say whether that is an issue for you?