By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

Foreach in MSSQL 2005

dcharnigo
P: 20
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.
Mar 31 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. select * into ForDeletionTmp from OrigTable where SomeConditionofAge = CertainValue
  2.  
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. delete from OrigTable 
  2. where CIFPan in (select CIFPan, count(*) as cnt from ForDeletionTmp group by CIFPan having count(*) > 1)
  3.  
Replace table names as neccesary.

-- CK
Mar 31 '08 #2

dcharnigo
P: 20
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
Apr 12 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Apr 12 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.