Sql-docs: Provide examples for insert of folders/files via T-SQL

[dcarapic]

Please provide examples on how to craft T-SQL statements to create folders or insert files.
How can I insert a file using T-SQL into specific folder? There is no example on how this can be done.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 23eebbdd-aa31-0716-094e-db743729d5ff
• Version Independent ID: 2dc6a267-25e4-46f9-af74-fe40756b6c0d
• Content: Access FileTables with Transact-SQL - SQL Server
• Content Source: docs/relational-databases/blob/access-filetables-with-transact-sql.md
• Product: sql
• Technology: filestream
• GitHub Login: @MikeRayMSFT
• Microsoft Alias: mikeray

[WilliamAntonRohm]

@dcarapic -- Dalibor, thank you for your feedback. Please see this article:

• Create, Alter, and Drop FileTables

Please also consider these resources:

• DBA Stack Exchange
• Stack Overflow

I am closing this issue now. You are welcome to @ mention me for any followup.
We hope to hear from you again.

cc @MikeRayMSFT for awareness

[dcarapic]

If you do not want to provide examples then please just state it.
You have linked an article which does not help at all (question is INSERT,
not Create, Alter or Drop Filetables).
Here, at least have this link somewhere so that people searching for help
can find some additional information:

https://stackoverflow.com/questions/10485978/creating-sub-directory-via-sql-insert-using-filetable

Thanks.

--
Dalibor Čarapić

On Tue, Jun 16, 2020 at 8:00 PM William Anton Rohm [e-mail]
wrote:

@dcarapic https://github.com/dcarapic -- Dalibor, thank you for your
feedback. Please see this article:

• Create, Alter, and Drop FileTables
  https://docs.microsoft.com/en-us/sql/relational-databases/blob/create-alter-and-drop-filetables

Please also consider these resources:

• DBA Stack Exchange
  https://dba.stackexchange.com/questions/tagged/sql-server
• Stack Overflow
  https://stackoverflow.com/questions/tagged/sql-server

I am closing this issue now. You are welcome to @ mention me for any
followup.
We hope to hear from you again.

cc @MikeRayMSFT https://github.com/MikeRayMSFT for awareness

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MicrosoftDocs/sql-docs/issues/4981#issuecomment-644919878,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ABM7OJCQFK5MSACG5X2MEZLRW6XKVANCNFSM4N7J425Q
.

[WilliamAntonRohm]

@dcarapic -- Dalibor, thank you for your follow-up.

@MikeRayMSFT -- Mike, please look into this issue.

[MikeRayMSFT]

@dcarapic - thank you for the submission. I'm reviewing this internally and we'll respond.

[MikeRayMSFT]

@dcarapic - I really appreciate your patience. I'm working on staging a comprehensive example. I don't want you to have to continue to wait though. While I'm creating and staging that, make sure you check out this helpful example: https://www.sqlshack.com/sql-server-filetable-the-next-generation-of-sql-filestream/

[dcarapic]

Hi Mike,
I managed to solve my issue thanks to the link I've already provided in my comment (https://stackoverflow.com/questions/10485978/creating-sub-directory-via-sql-insert-using-filetable). I've already scoured the internet to find help and I've seen the link that you've provided. Unfortunately that link does not provide information that I need. The main problem I had is how to manipulate the FILETABLE (CRUD) via the T-SQL or (.NET) code. All the documentation and all online help that I've seen focuses on manipulating the files via the File Explorer.
In my case we have a different scenario. The files are exposed via network file share but only as read-only and our custom application does all the managing of the files.

In essence what I really needed was how to generate the path_locator field (hierarchyid) correctly.
You have to generate it in order to insert a new record but there seems to be no 'standard' way to do it. In the link I've provided in my comment the path_locator is build up as a string by using GUID parts.
This works ... but is it the 'right' way to do it or is it a hack? It would be nice if official documentation would provide this information.

Since we are at it some more questions that I have:

• I figured out your could move a file to a different folder by T-SQL by simply setting new path_locator. Is it an 'OK' thing to do since it is a primary key of the table? I have no idea, again official documentation could be helpful here.
• I am writing file content in ADO.NET code by creating SqlCommand for INSERT/UPDATE with a parameter for the file content and then assigning a Stream as a parameter value. AFAIK this is standard way you work with large varbinary fields from ADO.NET. Is this the 'recommended' way of inserting/updaring FILETABLE content or is there an alternative.

Hopefully this comment provides you some insight into what I needed.

@WilliamAntonRohm
Sorry I was being snarky in my initial comment. It was very frustrating to work on this since it is a bit of an obscure functionality of SQL Server.

[MikeRayMSFT]

@dcarapic
I've sent your feedback to the PG and it's being reviewed.

Additional articles for you to consider are:

• https://docs.microsoft.com/sql/relational-databases/blob/work-with-directories-and-paths-in-filetables?view=sql-server-ver15
• https://docs.microsoft.com/sql/relational-databases/blob/load-files-into-filetables?view=sql-server-ver15