0

I am using a FileTable in SQL Server 2017 and I am doing a dating system to store files on it. Example:

Filetable directory\2020\1\19 - is today's directory, first the year, then the month, then the day. It all works great but only one thing left, checking whether it exists or not.

This is how I insert a directory:

INSERT INTO FILETBL (name, is_directory, is_archive, path_locator) 
VALUES (dirName, 1, 0, pathLocatorDependingOnParent)

It's hard to check for duplicates since the month of January (1) would exist in both 2019 and 2020 for example. Any idea how to check for duplicates while also considering subdirectories?

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
Osayed Zaheda
  • 1
  • 5
  • Not sure I understand your question. The `path_locator` column is the primary key for file tables. You should be able check for existence of the directory using `IF EXISTS(SELECT * FROM dbo.FILETBL WHERE path_locator = pathLocatorDependingOnParent)`. – Dan Guzman Jan 19 '20 at 15:20
  • The format of the path locator I use in the insert is different than the one used in the file table. Therefore I cannot search using it. – Osayed Zaheda Jan 19 '20 at 16:29
  • You can use GetPathLocator like: `WHERE path_locator = GetPathLocator(FileTableRootPath() + N'\Filetable directory\2020\1\19');` – Dan Guzman Jan 19 '20 at 16:40

0 Answers0