Загрузка в АИС заголовков фотодокументов, изначально забиваемых в Excel

ais-photo-docs-sync.sql

SET IDENTITY_INSERT [AISArchive].[dbo].[tblArchiveFiles] ON

/*
 *
 * ID архивного учреждения, в БД которого заливаются данные
 *
 */
DECLARE @ArchId INT
SET @ArchId = 4

DECLARE @whole_results TABLE(
 Id INT
 ,ItemId INT
 ,InventoryId INT
 ,Name NVARCHAR(MAX)
 ,Remark NVARCHAR(MAX)
 ,Number INT
 ,Letter NVARCHAR(255)
 ,DocumentsQuantity INT
 ,PagesQuantity INT
 ,ArchiveId INT
 ,nvarchar40 NVARCHAR(255)
 ,nvarchar41 NVARCHAR(255)
 ,nvarchar42 NVARCHAR(255)
 ,bit6 BIT 
)

/*
 * Сбор данных в одну кучу во временную таблицу.
 * Потом раскидается по нужным таблицам
 *
 */
DELETE FROM @whole_results;

INSERT INTO @whole_results (
 Id
 ,ItemId
 ,InventoryId
 ,Name
 ,Remark
 ,Number
 ,Letter
 ,DocumentsQuantity
 ,PagesQuantity
 ,ArchiveId
 ,nvarchar40
 ,nvarchar41
 ,nvarchar42
 ,bit6 
)
SELECT
  (IDENT_CURRENT('AISArchive.dbo.tblArchiveFiles') + ROW_NUMBER() OVER(ORDER BY UnitNumber)) as Id,
  (IDENT_CURRENT('AISArchive.dbo.tblArchiveFiles') + ROW_NUMBER() OVER(ORDER BY UnitNumber)) as ItemId,
  fc.InventoryId as InventoryId,
  fc.UnitName as Name,
  ISNULL(fc.UnitNote, '') as Remark,
  fc.UnitNumber as Number,
  ISNULL(fc.UnitLetter, '') as Letter,
  ISNULL(fc.UnitDocsCount, 1) as DocumentsQuantity,
  ISNULL(fc.UnitPagesCount, 1) as PagesQuantity,
  @ArchId as ArchiveId,
  ISNULL(fc.UnitPlace, '') as nvarchar40,
  ISNULL(fc.UnitDate, '') as nvarchar41,
  ISNULL(fc.UnitAuthor, '') as nvarchar42,
  0 as bit6
FROM
  [photodocs].[dbo].[cards] fc
WHERE
  NOT EXISTS (SELECT Id FROM AISArchive.dbo.tblArchiveFiles WHERE Deleted = 0 AND InventoryId = fc.InventoryId AND Number = fc.UnitNumber AND Letter LIKE ISNULL(fc.UnitLetter, ''))

/*
 * 
 * Вставить заголовки единиц хранения
 *
 */  
INSERT INTO [AISArchive].[dbo].[tblArchiveFiles] (
  [Id]
  ,[InventoryId]
  ,[Name]
  ,[Number]
  ,[Letter]
  ,[DocumentsStartDate]
  ,[DocumentsLastDate]
  ,[DocumentsQuantity]
  ,[PagesQuantity]
  ,[DocumentationTypeId]
  ,[ValueId]
  ,[MediaType]
  ,[Rubric1]
  ,[Rubric2]
  ,[Rubric3]
  ,[Rubric4]
  ,[Remark]
  ,[ShowToAll]
  ,[CreationTime]
  ,[DeletionTime]
  ,[Deleted]
  ,[ArchiveId]
  ,[ArchiveStorageId]
  ,[IsHidden]
)
 SELECT
  Id,
  InventoryId,
  Name,
  Number,
  Letter,
  NULL,
  NULL,
  DocumentsQuantity,
  PagesQuantity,
  6,
  1,
  1,
  '',
  '',
  '',
  '',
  Remark,
  0,
  CURRENT_TIMESTAMP,
  NULL,
  0,
  ArchiveId,
  1,
  0
  FROM @whole_results
  
/*
 *
 * Вставить дополнительную информацию по тематическим карточкам  
 *
 */
INSERT INTO [AISArchive].[dbo].[tblItemsAdditionalFieldsValues] (
 ItemId
 ,ItemType
 ,bit6
 ,nvarchar40
 ,nvarchar41
 ,nvarchar42
 ,ItemType2
)
 SELECT
  ItemId
  ,5
  ,0
  ,nvarchar40
  ,nvarchar41
  ,nvarchar42
  ,6
  FROM @whole_results