SSC-EWI-TS0032

Bulk Insert Partially Translated

The EWI is only generated when Javascript is the target language for Stored Procedures. This is a deprecated translation feature, as Snowflake Scripting is the recommended target language for Stored Procedures.

Severity

High

Generate Procedures and Macros using JavasScript as the target language adding the following flag -t JavaScript or --PLTargetLanguage JavaScript

Some parts of the output code are omitted for clarity reasons.

Description

This EWI is added to a literal that was originally a concatenation, when the contained code had a BULK INSERT statement. The PUT command resulting from the BULK INSERT translation is not supported when executing code that was originally Dynamic SQL.

For this reason, the PUT command must be extracted from the output code and executed manually outside of the procedure that contains it. Keep in mind that if there are many BULK INSERT statements in Dynamic SQL sentences within the procedure, it is advised to split this procedure to be able to manually execute the corresponding PUT command for each translated BULK INSERT.

Code Example

Input Code:

IN -> SqlServer_01.sql

-- Additional Params: -t JavaScript
CREATE PROCEDURE  [dbo].[Load_FuelMgtMasterData]
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE
            @SQLString VARCHAR(500)
        ,   @ImportName VARCHAR(200)
        ,   @Today DATE
        ,   @Yesterday DATE
        ,   @SourceAffiliates VARCHAR(200);

        SET @Today = GETDATE();
        SET @Yesterday = DATEADD(DAY, -1, @Today);
        TRUNCATE TABLE dbo.SourceFM_Affiliates;
        SET @ImportName = '\\' + +@@ServerName
            + '\WorkA\merchantportal\affiliates.txt';
        SET @SQLString = 'BULK INSERT ' + @SourceAffiliates + ' FROM '''
            + @ImportName + '''';
        EXEC (@SQLString);
    END;

Output Code:

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE dbo.Load_FuelMgtMasterData ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
    /*        SET NOCOUNT ON*/
    ;
    let SQLSTRING;
    let IMPORTNAME;
    let TODAY;
    let YESTERDAY;
    let SOURCEAFFILIATES;
    TODAY = SELECT(`   CURRENT_TIMESTAMP() :: TIMESTAMP`);
    YESTERDAY = SELECT(`   DATEADD(DAY, -1, ?)`,[TODAY]);
    EXEC(`TRUNCATE TABLE dbo.SourceFM_Affiliates`);
    IMPORTNAME = `\\` + SERVERNAME + `\WorkA\merchantportal\affiliates.txt`;
    SQLSTRING =
        // ** SSC-EWI-TS0032 - THE BULK INSERT WAS PART OF A DYNAMIC SQL, WHICH MAKES SOME OF THE TRANSLATED ELEMENTS INVALID UNLESS EXECUTED OUTSIDE DYNAMIC CODE. **
        `CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638461213351333410;

CREATE OR REPLACE STAGE STAGE_638461213351333410
FILE_FORMAT = FILE_FORMAT_638461213351333410;

PUT file://${IMPORTNAME} @STAGE_638461213351333410 AUTO_COMPRESS = FALSE;

COPY INTO ${SOURCEAFFILIATES} FROM @STAGE_638461213351333410/${IMPORTNAME}`;
    EXEC(`${SQLSTRING}`);
$$;

Recommendations

• Extract the PUT command that resulted from the Dynamic BULK INSERT statement, and execute it before calling the procedure.

• If you need more support, you can email us at [e-mail]