DECLARE @counter INT
SET @counter = 0
WHILE @counter < 10
BEGIN
declare @year varchar(4)
declare @week varchar(2)
DECLARE @sqlCommand varchar(2000)
DECLARE @sqlCommand2 varchar(2000)
declare @logdb varchar(100) = 'logs2' --<<<<<<<<<<<<<<<<<<<<<<
-- Begin transaction
BEGIN TRANSACTION;
BEGIN TRY
select top 1 @year = year([request_created_at]),@week= datepart(week,[request_created_at]) from dbo.request
where [request_created_at] < getutcdate()-14 order by [request_created_at]
print @year
print @week
set @sqlcommand ='select * into '+ @logdb+'.dbo.[request' +@year + right('0'+ @week,2) +'] from dbo.request where year([request_created_at])='+@year+ ' and datepart(week,[request_created_at])='+ @week
set @sqlcommand2 ='delete from dbo.request where year([request_created_at])='+@year+ ' and datepart(week,[request_created_at])='+ @week
exec (@sqlcommand)
exec (@sqlcommand2)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Rollback transaction on error
ROLLBACK TRANSACTION;
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
SET @counter = @counter + 1
END