stored procedure for archiving records

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)

stored procedure for archiving records

Author

Topic

CRP
Starting Member

10 Posts

Posted - 2008-03-27 : 02:35:21

I have two tables called A and B and C. Where A and C has the same schema

A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved

1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes

Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status

1 A 7/7/2007 No
3 B 2/4/2006 Yes

Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column

Pls provide me with full stored procedure code.

C.R.P RAJAN

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-27 : 02:41:55

>> Pls provide me with full stored procedure code.
I really hate it when people want code written for them rather than to learn how to do it.

select ta.taskid
into #a
from tablea ta
join tableb tb
on ta.taskid = tb.taskid
and ta.status = tb.status

insert c
select *
from tablea
where taskid in (select taskid from #a)

delete tablea
where taskid in (select taskid from #a)

not sure what the other columns mean and you should put in some error processing and transaction control but that's the gist.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources