Transfer multiple rows one at a time

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)

Transfer multiple rows one at a time

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-08-28 : 02:16:24

Hi,

I have the following scenario :

CustomerDetail
customerid
customername
status
app_no

[status = 0 means customer virtually deleted]

CustomerArchive
archiveno [autoincrement]
customerid
customername
status

At the end of the month, I have to physically delete customers. I have written two stored procs:

proc1

create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0

proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no

It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'

How can i transfer multiple rows one by one from the customerdetail to customerarchive using the same procs and then delete the rows once they are transferred.

I know i need to loop somehow but am not sure how to do it. Can you help me with some code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 02:47:55

You dont need two procedures. Use only second with the code

create proc spoc_insertcustomerarchive
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where status=0
delete from customerdetail where status=0

If you cant edit the procedure, then create new procedure with the aboce code

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources