ChurchKlerToAIS

gistfile1.txt

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

/*
 *
 * ID тематической БД клировых ведомостей
 *
 */
DECLARE @CatId INT
SET @CatId = 8

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

/*
 * Удаление имеющихся записей из БД.
 *
 */
 DELETE
 FROM [AISArchive].[dbo].[tblItemsAdditionalFieldsValues]
 WHERE
   ItemType = 9
   AND ItemId IN (
     SELECT
       Id
     FROM [AISArchive].[dbo].[tblThemeCards]
     WHERE
       ThemeCatalogId = @CatId
       AND ArchiveId = @ArchId
   )

DELETE
FROM [AISArchive].[dbo].[tblThemeCards]
WHERE
  ThemeCatalogId = @CatId
  AND ArchiveId = @ArchId

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)
 ,nvarchar7 NVARCHAR(255)
 ,nvarchar8 NVARCHAR(255)
 ,nvarchar9 NVARCHAR(255)
)

/*
 * Сбор данных в одну кучу во временную таблицу.
 * Потом раскидается по нужным таблицам
 *
 */
INSERT INTO @whole_results (
 Id
 ,ItemId
 ,ThemeCatalogId
 ,Content
 ,FundNumber
 ,InventoryNumber
 ,ArchiveFileNumber
 ,CreationTime
 ,DeletionTime
 ,Deleted
 ,ArchiveId
 ,SortCode
 ,nvarchar1
 ,nvarchar2
 ,nvarchar3
 ,nvarchar7
 ,nvarchar8
 ,nvarchar9
)
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,
 ('Н/п: '
     + LEFT(ISNULL(NULLIF(pt.[PoTypeName], '***'), ''), 1)
     + '. ' + ISNULL(NULLIF(p.[PointName], '***'), '-')
     + '. У: ' + ISNULL(NULLIF(u.[UezdName], '***'), '-')
     + '. Ц: ' + ISNULL(NULLIF(t.[TempleName], '***'), '-')
     + '. год: ' + ch.[Year]
     + '. лл: ' + ch.[Lists]) as Content,
 ch.[Fond] as FundNumber,
 ch.[Volume] as InventoryNumber,
 ch.[File] 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.[File]), 50)) as SortCode,
 (ISNULL(NULLIF(pt.[PoTypeName], '***'), '-')) as nvarchar1,
 (ISNULL(NULLIF(p.[PointName], '***'), '-')) as nvarchar2,
 (ISNULL(NULLIF(u.[UezdName], '***'), '-')) as nvarchar3,
 (ISNULL(NULLIF(t.[TempleName], '***'), '-')) as nvarchar7,
 ch.[Year] as nvarchar8,
 ch.[Lists] as nvarchar9
FROM
 [Church].[dbo].[Kler] 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 p.[status] = 1
 AND t.[status] = 1
 AND ch.[Fond] IS NOT NULL
 AND ch.[Fond] NOT LIKE '0%'
 AND ch.[Volume] NOT LIKE '0%'
 AND ch.[File] NOT LIKE '0%'
 AND ch.[TempleID] > 0
ORDER BY
  p.[PointName] ASC,
  pt.[PoTypeName] ASC,
  u.[UezdName] ASC,
  t.[TempleName] ASC,
  ch.[Fond] ASC,
  ch.[Volume] ASC,
  ch.[File] 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
 ,nvarchar7
 ,nvarchar8
 ,nvarchar9
)
 SELECT
  ItemId
  ,9 as ItemType
  ,nvarchar1
  ,nvarchar2
  ,nvarchar3
  ,nvarchar7
  ,nvarchar8
  ,nvarchar9
  FROM @whole_results