Looping through rows after a delete query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looping through rows after a delete query

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-04-30 : 04:42:16

I am doing a number of queries within a stored procedure. The stored procedure bulk imports data into table1

Then the first query is to update rows in table2 where already exists
The second query then deletes from table1 where it has already updated table2.

The problem then occurs when I try to loop through the remaining rows to do an insert.

My sql is as follows:

SELECT @CurrentRow = 1
SELECT @RowCount=COUNT(Id) FROM Table1

WHILE @CurrentRow <= @RowCount
BEGIN

INSERT INTO Table2
([Name],
[EmployerId],
[Description])
SELECT GC.Name,
GC.EmployerId, GC.Description

FROM Table1 GC
WHERE GC.Id = @CurrentRow

SELECT @NewId = ISNULL(SCOPE_IDENTITY(),0)
END

The problem is that because I have deleted rows the GC.Id = @CurrentRow gets out of sync.

eg. If I had 2 rows in table1 with Id 1 and 2. the updated statement updates Id 1 and then removes it leaving the table starting from 2 etc. which means the insert statement would not work as GC.Id = 2 and @CurrentRow = 1

Can someone suggest a better way from me to loop through rows in table1 after my delete. The update and delete statements need to be first.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-30 : 05:14:14

I don't see the need of cursor here.

1. Bulk Insert into table1
2. update table2 from table1


update t2
set    t2col = t1col
from   table2 t2 inner join table1 t1 on t2.pk = t1.pk

3. insert into table2 from table1


insert into table2 ( . . . )
select . . .
from   table1 t1
where  not exists 
       (
           select *
           from   table2 x
           where  x.pk = t1.pk
       )

3. delete table1
delete table1

or you may use truncate table

KH
[spoiler]Time is always against us[/spoiler]

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-04-30 : 05:18:14

I was looping through each one as I need to get the inserted id from each (SELECT @NewId = ISNULL(SCOPE_IDENTITY(),0))as then I insert into other child tables.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-30 : 07:05:01

can you show us the insert into child statement ?

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-30 : 07:41:32

you could use an OUTPUT clause to store the information you require from the DELETE into a table variable. Then use that table variable to work your inserts.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-04-30 : 09:17:37

thanks for advice - I have used a work around - I have inserted a bit field in table1 which I set to 1 for all updated rows rather than delete them at this point and then I do the inserts and then delete the updated rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources