Foreach in MSSQL 2005

[dcharnigo]

I am writing a purge routine for a database that I have. I select all records that have aged to a certain threshold into a temp table, now I want to remove some of these items, then in the end after everything is sorted remove the remaining records from the original table. Each record has a unique guid.

So to delete at the end I think I just need to do:

DELETE * FROM orig_table where orig_table.guid IN temp_table

My Question is after I select the records into the temp table I need a count foreach record on field CIFPan, in C# I would do something like this:

foreach ( CIFPan var in temp_table ) {
@count = select count(*) from orginal_table where CIFPan = var
if @count > 1 DELETE * FROM orig_table WHERE CIFPan = var
}

Obviously just sudo code but can I do something like that in the SQL server stored procedure? I was thinking maybe Some sort of UNION/JOIN/SELECT combination?

Thanks for the Help.

[ck9663]

I am writing a purge routine for a database that I have. I select all records that have aged to a certain threshold into a temp table, now I want to remove some of these items, then in the end after everything is sorted remove the remaining records from the original table. Each record has a unique guid.

So to delete at the end I think I just need to do:

DELETE * FROM orig_table where orig_table.guid IN temp_table

My Question is after I select the records into the temp table I need a count foreach record on field CIFPan, in C# I would do something like this:

foreach ( CIFPan var in temp_table ) {
@count = select count(*) from orginal_table where CIFPan = var
if @count > 1 DELETE * FROM orig_table WHERE CIFPan = var
}

Obviously just sudo code but can I do something like that in the SQL server stored procedure? I was thinking maybe Some sort of UNION/JOIN/SELECT combination?

Thanks for the Help.

Try:

select * into ForDeletionTmp from OrigTable where SomeConditionofAge = CertainValue
 
select CIFPan, count(*) as cnt from ForDeletionTmp group by CIFPan 

This code is not complete. I left the part where you delete the records from your Original Table. Your code shows that you only delete records from Original Table if there are multiple records (based on CIFPan). If there's only one record, it looks like you're going to keep the record if @count > 1 DELETE * FROM orig_table WHERE CIFPan = var is this right?

If yes, proceed with this additional code:

delete from OrigTable 
where CIFPan in (select CIFPan, count(*) as cnt from ForDeletionTmp group by CIFPan having count(*) > 1)
 

Replace table names as neccesary.

-- CK

[dcharnigo]

Thanks for the help, You gave me a good start I will get it to work. I guess whenever I need to do a "foreach" I need to select into a temp table and then execute the next select on the temp table.

Thanks,

Dan

[ck9663]

Yes. But depending on what you need, there are other ways you can do it. So if you hit a wall, post it here and we'll try to crack it.

Happy Coding.

-- CK