开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜