How to delete 1000 rows at a time with SQL Server OPENQUERY

151 views Asked by At

I need to delete rows in a link server's table, 1000 rows at a time.

My code is the following:

WHILE (SELECT COUNT(*) FROM OPENQUERY (SqlServerAcc, 'SELECT * FROM titles ') > 1000)
BEGIN
    DELETE OPENQUERY (SqlServerAcc,
       'SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM tbl) t WHERE RowNumber <= 1000');
END

But I do have errors:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '>'.

at the expression > 1000.

I don't understand why this does not work.

Bonus: how can we make the name of the server not hard-coded; in PRD, it should be SqlServerPrd, but it seems that OPENQUERY does not accept variables for its arguments...

0

There are 0 answers