1

Topic: To interpose a file @name into table FileTable in a subdirectory @dir (the first level)

To interpose a file @name into table FileTable
In a subdirectory @dir (the first level).
It works. But it would be desirable interposes "directly". Without update.
There are ideas?

begin transaction;
if not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null)
insert TourML.SpoFiles (is_directory, name)
select 1, @dir
where not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null);
declare @id table (stream_id uniqueidentifier);
insert TourML.SpoFiles (name, file_stream)
output inserted.stream_id into @id (stream_id)
select cast (newid () as nvarchar (255)), cast (@XML as varbinary (max));
with fo as (select path_locator from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null)
, fl as (select f.* from TourML.SpoFiles as f inner join @id as t on t.stream_id = f.stream_id where f.is_directory = 0)
update fl set path_locator = fl.path_locator. GetReparentedValue (hierarchyid:: GetRoot (), fo.path_locator), name = @name
from fl cross join fo;
commit transaction;

2

Re: To interpose a file @name into table FileTable in a subdirectory @dir (the first level)

All right, I won

begin transaction;
if not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null)
insert TourML.SpoFiles (is_directory, name)
select 1, @dir
where not exists (select * from TourML.SpoFiles where is_directory = 1 and name = @dir and parent_path_locator is null);
with f as (select * from TourML.SpoFiles)
, fl as (select * from f where is_directory = 0 and file_type = N'xml ' and name = @name)
, fo as (select * from f where is_directory = 1 and name = @dir and parent_path_locator is null)
, x as (select path_locator = isnull ((select path_locator from fl where parent_path_locator = fo.path_locator)
, fo.path_locator. GetDescendant ((select max (path_locator) from f where parent_path_locator = fo.path_locator), null)
)
, name = @name
, file_stream = cast (@XML as varbinary (max))
, creation_time = @now
from fo
)
merge f using x on f.path_locator = x.path_locator
when not matched then insert (path_locator, name, file_stream, creation_time) values (path_locator, name, file_stream, creation_time)
when matched and (f.file_stream <> x.file_stream) then update set file_stream = x.file_stream, creation_time = @now
;
commit transaction;