Execute multiple sql statements in the ADF without batch script and stored procedure

Question

Recently, I have been tangled to a situation where I am neither allowed to use the batch service for executing python scripts nor to execute stored procedure.

The pipeline which I am trying to achieve is: Blob --> Staging --> Some SQL transformation --> Loading into final target table into the Snowflake

Dummy SQL: TRUNCATE TABLE CDWD.USERS.EE_NEW_LAUNCHES_MAST command 1

INSERT INTO Table 1 command 2

(

some fields

)

WITH CTE AS

(

SELECT CYAGG.TIME_PERIOD_NO,

field1, field2, derived field 1, computed field 2, transformed field 3 (example Substring (Table2.field1,1,20) etc

From AnotherTable
Join AnotherTable2

) Select * from CTE;

select 1 as id; (If this was lookup activity)

Would really appreciate the help.

Answer 1

Hello @Mahima Lalwani ,
Thanks for the ask and using the Microsoft Q&A platform .

I think the intend is by using ADF but before you run the select query for lookup you want to get some other things done and one of them is to truncate a table also .

-- Data prep
CREATE TABLE to_be_trucated
( id int )
INSERT INTO to_be_trucated VALUES(1)

-- You are trying this
Truncate table to_be_trucated
GO
with CTE AS
(
select colB,ColC from [dbo].[someothertable] )
Select * from CTE

You can use the lookup activity and use the query option and add the below script

Truncate table to_be_trucated ;
with CTE AS
(
select colB,ColC from [dbo].[someothertable] )
Select * from CTE

Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members