question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked ·

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

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.

sql-server-transact-sql
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If this is not what you need:

 declare @script nvarchar(max) = '
 DELETE FROM MyDB.dbo.VCardService WHERE MFRef = @myref
 DELETE FROM MyDB.dbo.ScheduleChangeQueues WHERE MFRef = @myref
 . . .'
    
 declare @myref int = 1001
    
 execute sp_executesql @script, @params = N'@myref int', @myref=@myref

then give some more details.

0 Votes 0 ·

Hi @balanarasimhac-1495,

Could you please validate all the answers so far and provide any update?

Best regards
Melissa

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @balanarasimhac-1495 ,

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.




controlflow.png (23.4 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @balanarasimhac-1495,

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.