Переносит метрические книги из mdb church в АИС Архив. Выводит список заголовков, не попавших в базу АИС Архив.

ChurchMetricsToAIS

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

/*
 *
 * ID тематической БД метрических книг
 *
 */
DECLARE @CatId INT
SET @CatId = 2

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

DECLARE @whole_results TABLE(
 Id INT
 ,ItemId INT
 ,ThemeCatalogId INT
 ,Content VARCHAR(740)
 ,FundNumber NVARCHAR(255)
 ,InventoryNumber NVARCHAR(255)
 ,ArchiveFileNumber NVARCHAR(255)
 ,CreationTime DATETIME
 ,DeletionTime DATETIME
 ,Deleted BIT
 ,ArchiveId INT
 ,SortCode VARCHAR(160)
 ,nvarchar1 NVARCHAR(255)
 ,nvarchar2 NVARCHAR(255)
 ,nvarchar3 NVARCHAR(255)
 ,nvarchar4 NVARCHAR(255)
 ,nvarchar5 NVARCHAR(255)
 ,nvarchar6 NVARCHAR(255)
 ,nvarchar7 NVARCHAR(255)
 ,nvarchar8 NVARCHAR(255)
)

/*
 * Сбор данных в одну кучу во временную таблицу.
 * Потом раскидается по нужным таблицам
 *
 */
INSERT INTO @whole_results (
 Id
 ,ItemId
 ,ThemeCatalogId
 ,Content
 ,FundNumber
 ,InventoryNumber
 ,ArchiveFileNumber
 ,CreationTime
 ,DeletionTime
 ,Deleted
 ,ArchiveId
 ,SortCode
 ,nvarchar1
 ,nvarchar2
 ,nvarchar3
 ,nvarchar4
 ,nvarchar5
 ,nvarchar6
 ,nvarchar7
 ,nvarchar8
)
SELECT
 (IDENT_CURRENT('AISArchive.dbo.tblThemeCards') + ROW_NUMBER() OVER(ORDER BY Fond)) as Id,
 (IDENT_CURRENT('AISArchive.dbo.tblThemeCards') + ROW_NUMBER() OVER(ORDER BY Fond)) as ItemId,
 @CatId as ThemeCatalogId,
 ('Населенный пункт: ' + ISNULL(NULLIF(pt.[PoTypeName], '***'), '')
  + ' ' + ISNULL(NULLIF(p.[PointName], '***'), '-')
  + '. Уезд: ' + ISNULL(NULLIF(u.[UezdName], '***'), '-')
  + '. Церковь: ' + ISNULL(NULLIF(t.[TempleName], '***'), '-')) as Content,
 ch.[Fond] as FundNumber,
 ch.[Volume] as InventoryNumber,
 ch.[Nom] as ArchiveFileNumber,
 CURRENT_TIMESTAMP as CreationTime,
 NULL as DeletionTime,
 0 as Deleted,
 @ArchId as ArchiveId,
 ('ф.' + RIGHT('000000000000000000000000000000000000000000000000' + CONVERT(VARCHAR, ch.[Fond]), 50)
  + ' оп.' + RIGHT('000000000000000000000000000000000000000000000000' + CONVERT(VARCHAR, ch.[Volume]), 50)
  + ' д.' + RIGHT('000000000000000000000000000000000000000000000000' + CONVERT(VARCHAR, ch.[Nom]), 50)) as SortCode,
 (ISNULL(NULLIF(pt.[PoTypeName], '***'), '-')) as nvarchar1,
 (ISNULL(NULLIF(p.[PointName], '***'), '-')) as nvarchar2,
 (ISNULL(NULLIF(u.[UezdName], '***'), '-')) as nvarchar3,
 ch.[Birth] as nvarchar4,
 ch.[Weddings] as nvarchar5,
 ch.[Death] as nvarchar6,
 (ISNULL(NULLIF(t.[TempleName], '***'), '-')) as nvarchar7,
 ch.[Year] as nvarchar8
FROM
 [Church].[dbo].[Metrics] ch
LEFT JOIN [Church].[dbo].[Points] p ON
 p.[PointID] = ch.[PointID]
LEFT JOIN [Church].[dbo].[PointsType] pt ON
 pt.[PoTypeID] = p.[PointType]
LEFT JOIN [Church].[dbo].[Uezd] u ON
 u.[UezdID] = ch.[UezdID]
LEFT JOIN [Church].[dbo].[Temples] t ON
 t.[TempleID] = ch.[TempleID]
WHERE
 ch.[status] = 1
 AND NOT EXISTS (
  SELECT *
  FROM
   [AISArchive].[dbo].[tblThemeCards] tc
  WHERE
   tc.FundNumber = ch.Fond
   AND tc.InventoryNumber = ch.Volume
   AND tc.ArchiveFileNumber = ch.Nom
   AND tc.ThemeCatalogId = @CatId
   AND tc.Deleted = 0
   AND tc.ArchiveId = @ArchId
   AND tc.Content =
    ('Населенный пункт: ' + ISNULL(NULLIF(pt.[PoTypeName], '***'), '')
    + ' ' + ISNULL(NULLIF(p.[PointName], '***'), '-') + '. Уезд: '
    + ISNULL(NULLIF(u.[UezdName], '***'), '-')
    + '. Церковь: ' + ISNULL(NULLIF(t.[TempleName], '***'), '-'))
 )
 AND ch.[Fond] IS NOT NULL
 AND ch.[Fond] NOT LIKE '0%'
 AND ch.[Volume] > 0
 AND ch.[File] > 0
 AND ch.[UezdID] > 0
 AND ch.[TempleID] > 0
 AND p.[PointType] > 0
ORDER BY
  p.[PointName] ASC,
  pt.[PoTypeName] ASC,
  u.[UezdName] ASC,
  t.[TempleName] ASC,
  ch.[Fond] ASC,
  ch.[Volume] ASC,
  ch.[File] ASC,
  ch.[Leter] ASC

/*
 * 
 * Вставить тематические карточки
 *
 */  
INSERT INTO [AISArchive].[dbo].[tblThemeCards] (
 Id
 ,ThemeCatalogId
 ,Content
 ,FundNumber
 ,InventoryNumber
 ,ArchiveFileNumber
 ,CreationTime
 ,DeletionTime
 ,Deleted
 ,ArchiveId
 ,SortCode
)
 SELECT
  Id
  ,ThemeCatalogId
  ,Content
  ,FundNumber
  ,InventoryNumber
  ,ArchiveFileNumber
  ,CreationTime
  ,DeletionTime
  ,Deleted
  ,ArchiveId
  ,SortCode
  FROM @whole_results

/*
 *
 * Вставить дополнительную информацию по тематическим карточкам  
 *
 */
INSERT INTO [AISArchive].[dbo].[tblItemsAdditionalFieldsValues] (
 ItemId
 ,ItemType
 ,nvarchar1
 ,nvarchar2
 ,nvarchar3
 ,nvarchar4
 ,nvarchar5
 ,nvarchar6
 ,nvarchar7
 ,nvarchar8
)
 SELECT
  ItemId
  ,9 as ItemType
  ,nvarchar1
  ,nvarchar2
  ,nvarchar3
  ,nvarchar4
  ,nvarchar5
  ,nvarchar6
  ,nvarchar7
  ,nvarchar8
  FROM @whole_results
  
/*
 *
 * Вывести список невошедших в базу записей.
 *
 */
SELECT
 ('Населенный пункт: ' + ISNULL(NULLIF(pt.[PoTypeName], '***'), '')
  + ' ' + ISNULL(NULLIF(p.[PointName], '***'), '-')
  + '. Уезд: ' + ISNULL(NULLIF(u.[UezdName], '***'), '-')
  + '. Церковь: ' + ISNULL(NULLIF(t.[TempleName], '***'), '-')) as Content,
 ch.[Fond] as FundNumber,
 ch.[Volume] as InventoryNumber,
 ch.[Nom] as ArchiveFileNumber,
 ch.[Birth] as Birth,
 ch.[Weddings] as Weddings,
 ch.[Death] as Death,
 ch.[Year] as Year,
 ch.[UezdID] as UezdID,
 ch.[TempleID] as TempleID,
 ch.[PointID] as PointID,
 p.[PointType] as PointType
FROM
 [Church].[dbo].[Metrics] ch
LEFT JOIN [Church].[dbo].[Points] p ON
 p.[PointID] = ch.[PointID]
LEFT JOIN [Church].[dbo].[PointsType] pt ON
 pt.[PoTypeID] = p.[PointType]
LEFT JOIN [Church].[dbo].[Uezd] u ON
 u.[UezdID] = ch.[UezdID]
LEFT JOIN [Church].[dbo].[Temples] t ON
 t.[TempleID] = ch.[TempleID]
WHERE
 (ch.[status] = 1
 AND NOT EXISTS (
  SELECT *
  FROM
   [AISArchive].[dbo].[tblThemeCards] tc
  WHERE
   tc.FundNumber = ch.Fond
   AND tc.InventoryNumber = ch.Volume
   AND tc.ArchiveFileNumber = ch.Nom
   AND tc.ThemeCatalogId = @CatId
   AND tc.Deleted = 0
   AND tc.ArchiveId = @ArchId
   AND tc.Content =
    ('Населенный пункт: ' + ISNULL(NULLIF(pt.[PoTypeName], '***'), '')
    + ' ' + ISNULL(NULLIF(p.[PointName], '***'), '-')
    + '. Уезд: ' + ISNULL(NULLIF(u.[UezdName], '***'), '-')
    + '. Церковь: ' + ISNULL(NULLIF(t.[TempleName], '***'), '-'))
 ))
 AND (ch.[Fond] IS NULL
 OR ch.[Fond] LIKE '0%'
 OR ch.[Volume] < 1
 OR ch.[File] < 1
 OR ch.[UezdID] < 1
 OR ch.[TempleID] < 1
 OR p.[PointType] < 1)
ORDER BY
  p.[PointName] ASC,
  pt.[PoTypeName] ASC,
  u.[UezdName] ASC,
  t.[TempleName] ASC,
  ch.[Fond] ASC,
  ch.[Volume] ASC,
  ch.[File] ASC,
  ch.[Leter] ASC