Incorrect syntax near the keyword 'begin'
I'm trying to remove large number of rows from a table and avoid making the transact log too big at the same time. It seems that most of the people on the internet recommend using BEGIN TRAN/COMMIT TRAN statement. I'm not really an expert on SQL so I just tried to translate the phseudocode into ms sql (SQL Server 2005). Here is the code:
set rowcount 1000
while (1=1)
BEGIN
with MonthEndDates as (
select max(Rundate) MonthEnd
from table3
group by convert(varchar(6), RunDate, 112))
begin transaction T1
delete from table1 where
table2id in
(select table2id from table2 where
table3id in
(select table3id from table3
where RunDate < getdate()-30
and RunDate not in (select MonthEnd from M开发者_Python百科onthEndDates)))
commit transaction T1
if @@rowcount = 0
break
END
set rowcount 0
Am I doing this right at all? If yes, why am I getting this error: Incorrect syntax near the keyword 'begin'. I also tried removing the transaction labels, but it didnt help
As I said I'm not good in SQL so any help would be appreciated
Thanks
set rowcount 1000
while (1=1)
BEGIN
begin transaction T1
--The CTE goes with the DELETE
;with MonthEndDates as (
select max(Rundate) MonthEnd
from table3
group by convert(varchar(6), RunDate, 112))
delete from table1 where
table2id in
(select table2id from table2 where
table3id in
(select table3id from table3
where RunDate < getdate()-30
and RunDate not in (select MonthEnd from MonthEndDates)));
commit transaction T1
if @@rowcount = 0
break
END
set rowcount 0
精彩评论