469,360 Members | 2,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

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.
Mar 31 '08 #1
3 4209
ck9663
2,878 Expert 2GB
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
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
2,878 Expert 2GB
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.

Similar topics

7 posts views Thread by mj | last post: by
1 post views Thread by theintrepidfox | last post: by
reply views Thread by Gosth in the shell | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.