About Cursors

Transact-SQL
1

I have a problem to delete records in a database its having 20 tables. these tables are deleted by using cursors . how can i write a cursor to delete all the records from tables at a time.

2

why would you want to use cursor to do that.

You can simply

DELETE child_table WHERE pk_column = 'some value'
DELETE parent_table WHERE pk_column = 'some value'
1 Like
3

Hi nagaraju,
Since you want to delete all data from the tables, you can simply truncate

truncate table tableA

As you want to do it for a set of tables, I assume not all tables from the DB, so you will need a way to find out all the tables from a query, then you can either use a while loop, or cursors and perform truncate for each.

Hope it helps.

1 Like
4

At the same time have to delete all the records at a same time . i.e i need cursor to delete the all the tables of data.

5

that is not possible. A DELETE statement can only delete records from one table only. You will required multiple delete statement.

You can execute these delete statements in a transaction if this is your concern. So if any one fail, the transaction will be rollback

1 Like
6

By writing Stored procedure is there any way to truncate tables at a time.

7

Yes, the safest way is to use a stored procedure. Be sure to enclose all the DELETEs in a single transaction. That way you don't have to use cursors. Cursors and DELETEs run very slowly compared to plain DELETEs. You can use the OUTPUT clause to capture key values from one DELETE to use in the next DELETE if you need to. Here's the general structure of such a proc:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.DeleteFromMultiTables
    @param1 ...
AS
SET NOCOUNT ON;
CREATE TABLE #table1_key_cols (
    col1 ...,
    col2 ...
    )
BEGIN TRY;
BEGIN TRANSACTION;
DELETE FROM dbo.table1 
OUTPUT DELETED.col1, DELETED.col2 INTO #table1_key_cols ( col1, col2 )
WHERE ... = ...
DELETE FROM t2
FROM dbo.table2 t2
INNER JOIN #table1_key_cols t1kc ON
    t1kc.col1 = t2.col1
--...DELETE from other tables
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
--...error code
END CATCH
GO --end of proc
1 Like