How to take destination output as input for execute sql task in SQL

Bala Narasimha Challa 466 Reputation points
2021-03-03T16:53:03.21+00:00

Hi Team,
Am writing a SQL query to generate delete script as show in as shown in bellow.

DELETE FROM MyDB.dbo.VCardService WHERE MFRef =1000;
DELETE FROM MyDB.dbo.ScheduleChangeQueues WHERE MFRef =1000;
DELETE FROM MyDB.dbo.Riskified WHERE bookingRef =1000;
DELETE FROM MyDB.dbo.ReissuedBookings WHERE bookingRef =1000;
DELETE FROM MyDB.dbo.RefundRequest WHERE bookingRef =1000;

I want to take this script in SQL execute task and need to execute one by one entry.

Please help on this issue.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-04T02:31:35.943+00:00

    Hi @Bala Narasimha Challa ,

    Please drag 5 Execute SQL Tasks and connect them.

    Then the Execute SQL Tasks will be executed by running the ssis package.

    74071-controlflow.png

    Best regards
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-03-04T02:45:05.25+00:00

    Hi @Bala Narasimha Challa ,

    I want to take this script in SQL execute task and need to execute one by one entry.

    What does 'SQL execute task' refer? Would you like to execute this script in SSIS, maintenance plan or other tool?

    From TSQL side, you could refer below and check whether it is working. 

    drop table if exists ##temp  
  
declare @script nvarchar(max) = '  
DELETE FROM MyDB.dbo.VCardService WHERE MFRef =1000;  
DELETE FROM MyDB.dbo.ScheduleChangeQueues WHERE MFRef =1000;  
DELETE FROM MyDB.dbo.Riskified WHERE bookingRef =1000;  
DELETE FROM MyDB.dbo.ReissuedBookings WHERE bookingRef =1000;  
DELETE FROM MyDB.dbo.RefundRequest WHERE bookingRef =1000;'  
  
select IDENTITY(int,1,1) ID,*   
into ##temp  
from string_split(@script,';')  
where isnull(value,'')<>''  
  
declare @i int=1  
declare @max int  
declare @sql nvarchar(1000)=''  
select @max=count(*) from ##temp   
  
while @i<=@max  
begin	  
	select @sql=value from ##temp where id=@i  
	BEGIN TRANSACTION  
		execute sp_executesql  @sql--execute the delete statement	         
	COMMIT TRANSACTION   
	WAITFOR DELAY '00:00:01' --add some wait time if ncessary   
	set @sql=''  
	set @i =@i+1      
end

    If your SQL Server is pre-2016, you could refer below user-defined function instead of string_split. 

    CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
    );

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments