Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
aleks222
Guest |
Вставить файл @name в таблицу FileTable в субдиректорию @dir (первый уровень ). Это работает. Но хочется вставит "прямо". Без update. Есть идеи? 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; |
31 янв 18, 21:08 [21155715] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
Ладно, я победил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; |
1 фев 18, 17:58 [21158544] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |