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.