What is the best way of removing a cursor from SQL procedure if we have large amount of data to process

Question

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

Answer 1

You should always CLOSE the cursor variable and then DEALLOCATE the cursor variable with

CLOSE {MyCursorVariableNameHere}
DEALLOCATE  {MyCursorVariableNameHere}

Example:

USE tempdb;
GO
declare @c cursor;

set @c = CURSOR LOCAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4;

OPEN @c;
GO
SELECT * FROM sys.dm_exec_cursors(0);
GO
declare c CURSOR GLOBAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4;

OPEN c;
GO
SELECT * FROM sys.dm_exec_cursors(0);
GO
CLOSE c;
DEALLOCATE c;
GO
declare c CURSOR LOCAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4;

OPEN c;
GO
SELECT * FROM sys.dm_exec_cursors(0);
GO

AMB