用户对问题“Azure synapse分析并行插入临时表”的回答

Azure synapse分析并行插入临时表

CREATE PROC [staging].[usp_stg_load_SrcChroniquesPGA] @file_name 
[varchar](100) AS
BEGIN
DECLARE @json nvarchar(max) = (select json_file from [staging]. 
[xml_to_json] where [file_name] = @file_name) ;
INSERT INTO [staging].[SrcChroniquesPGA]
select 
            @file_name as [NomFichier]
            ,[DatePublication],[NomSource],[Pas],[Type],[DateChronique]
            ,CASE WHEN Pas = 'H' AND DATEPART(hh, DateChronique) BETWEEN 0 AND 5 THEN DATEADD(day, -1, CAST(DateChronique AS DATE)) 
                    ELSE CAST(DateChronique AS DATE) END 
             AS [DateJourneeGaziere]
            ,[HorodateMaj],[Qualite]
            ,[ValeurChronique],[DateStatut],[HorodateMajStatut],[ValeurStatut]
    from OPENJSON( @json ,'$') 
    WITH (
        [DatePublication] [datetime2](7) '$.Transaction.HorodateInfos',
        [PGA] nvarchar(MAX) '$.PGA' as JSON
    CROSS APPLY OPENJSON(PGA)
    WITH (
        [NomSource] [nvarchar](256) '$.PCEIdentification.NomSource',
        [PGAStatut] nvarchar(MAX) '$.PGAStatut' as JSON,
        [PGAChronique] nvarchar(MAX) '$.PGAChronique' as JSON
    CROSS APPLY OPENJSON(PGAChronique)
    WITH (
        [Pas] [nvarchar](256) '$.Pas',
        [Type] [nvarchar](256) '$.Type',
        --[Unite] nvarchar(100) '$.Unite',
        [Mesure]  nvarchar(MAX) '$.Mesure' as JSON
    CROSS APPLY OPENJSON([Mesure])
    WITH (
        [DateChronique] [datetime2](7) '$.Date',
        [ValeurChronique] [float] '$.Valeur',
        [HorodateMaj] [datetime2](7) '$.HorodateMaj',
        [Qualite] [nvarchar](256) '$.Qualite'
    ) msr
    OUTER APPLY (
            SELECT * FROM OPENJSON(PGAStatut)
            WITH (
                [DateStatut] [datetime2](7) '$.Date',
                [HorodateMajStatut] [datetime2](7) '$.HorodateMaj',
                [ValeurStatut] [nvarchar](256) '$.Valeur'