How to create .SQL file and how to run multiple queries present inside the .SQL file one by one

Help Studio
, ,
1

Hi Team,

Please help us on how to create .SQL file & also how to run sql queries present inside the .SQL file one by one.

I have created below flow for to run .SQL file,
{1. Read SQL File:
Use the “Read Text File” activity to read the content of the SQL file into a string variable.

2.Split Queries:

Split the content of the file into individual queries.

queriesArray = sqlFileContent.Split({“;”}, StringSplitOptions.RemoveEmptyEntries)

3.Database Connection:

Establish a database connection using the “Connect” activity

4.Use a “For Each” activity to iterate through the array of queries.

For Each query In queriesArray

Try

Execute Non Query (using query as SQL command)

Catch (Exception)

Log or handle the exception

End Try}

I didn’t get expected output from the above flow.

2

Hi @Smitesh_Aher2

It will be best if you create the stored procedure which includes all the queries. You can put all the queries directly into the stored procedure as strings and execute them within the procedure itself.

You can run this procedure using execute query or run query by selecting command type as stored procedure.

Hope this helps :slight_smile:

3

Hi @AJ_Ask

Thanks for response. Can you give me steps for how to create stored procedure?

5

Hi @Smitesh_Aher2

This is the basic structure which you can modify as per your requirement

CREATE PROCEDURE ExecuteQueries
AS
BEGIN
DECLARE @Query1 NVARCHAR(MAX) = ‘Your SQL Query 1’;
DECLARE @Query2 NVARCHAR(MAX) = ‘Your SQL Query 2’;
– Add more DECLARE statements for additional queries if needed

BEGIN TRY

    -- Execute Query 1

    EXEC sp_executesql @Query1;
    PRINT 'Query 1 executed successfully';

    -- Execute Query 2

    EXEC sp_executesql @Query2;
    PRINT 'Query 2 executed successfully';

    -- Add more EXEC sp_executesql statements for additional queries as needed

END TRY
BEGIN CATCH
    PRINT 'An error occurred while executing queries';
    PRINT ERROR_MESSAGE();
END CATCH

END

For Ref:

https://youtu.be/MSbzErdcb6g?si=ahCtXrFQ5oTxD0mS

Hope this helps :slight_smile:

6

Hi @Darshit_Vekariya Thanks for response.

Can you please send steps for how to create .SQL file?

7

Not understand highlighted above portion.