I have a SQL Server 2014 FileTable, which contains directories and files. I have been tasked with writing a stored procedure that will copy a directory and all its contents recursively from one location to another within the FileTable. However, this is for an ASP.NET web application, so if the stored procedure isn't possible or is too complex, I could implement a different solution, possibly involving copying the files using the UNC path.
First of all, is this even possible? Someone I work with was thinking that you can't copy the filestream blob.
Anyway, if it is possible, how would you go about it?
Here is my script to create the table:
CREATE TABLE [dbo].[DocLibrary] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [DSTKOL_FileGroup]
WITH
(
FILETABLE_DIRECTORY = N'DocLibrary', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS,
FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=[UQ_DocLibrary_stream_id]
)
ALTER TABLE [dbo].[DocLibrary] WITH CHECK ADD CONSTRAINT [CK_DocLibrary_NoRootFiles] CHECK (([is_directory]=(1) OR [path_locator].[GetLevel]()>(2)))
ALTER TABLE [dbo].[DocLibrary] CHECK CONSTRAINT [CK_DocLibrary_NoRootFiles]
And here is what I have so far of the stored procedure:
CREATE PROCEDURE [dbo].[procCopyItemDocLibrary]
@SourcePath varchar(max),
@DestPath varchar(max)
AS
BEGIN
BEGIN TRANSACTION;
DECLARE @SuccessCode int = 0;
DECLARE @SourceItemId hierarchyid = GETPATHLOCATOR(FILETABLEROOTPATH('DocLibrary') + @SourcePath);
IF @SourceItemId IS NOT NULL
BEGIN
DECLARE @DestParentId hierarchyid = GETPATHLOCATOR(FILETABLEROOTPATH('DocLibrary') + @DestPath);
IF @DestParentId IS NOT NULL
BEGIN
DECLARE @FolderName varchar(max);
SELECT @FolderName = name FROM dbo.DocLibrary WHERE path_locator = @SourceItemId;
DECLARE @FOLDEREXISTS BIT = dbo.funcCheckExistsDocLibrary(@DestParentId, @FolderName);
IF @FOLDEREXISTS = 0
BEGIN
DECLARE @SourceParentId hierarchyid = @SourceItemId.GetAncestor(1);
-- ???
END
ELSE SET @SuccessCode = 3;
END
ELSE SET @SuccessCode = 2;
END
ELSE SET @SuccessCode = 1;
COMMIT TRANSACTION;
RETURN @SuccessCode;
END