move records between tables-URGENT!

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 2000 Forums

SQL Server Development (2000)

move records between tables-URGENT!

Author

Topic

kenone
Starting Member

14 Posts

Posted - 2006-03-06 : 02:06:47

I have a one to one relationship between two tables(members and pensioners) in SQL server with vb.net at the front end.The concept is when a member attains retirement age (60yrs) he should become inactive (but not deleted)in members table and his/her records should be automatically moved to the pensioners table so that can be accessed through pensioners form.I have an idea of creating a command button (on click event) with SQL statements to do this (though I dont know how)but any suggestions are highly welcome.....

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-06 : 02:14:06

Hi,
Do you want to do it manually or you want it automatically?
For this you can write down a stored proc which will check the dateofbirth with current date and if it is 60 then can movethe record to pensioner..

Create proc MovetoPensioners as
insert into Pensioners (<column names>)
select <columnnames>
from members
where datedif(yy,getdate(),dateofbirth) >=60

delete members
where datedif(yy,getdate(),dateofbirth) >=60

GO

if you want to do this manually call this stored proc on click of command button else schedule this stored procedure daily.

Kristen
Test

22859 Posts

Posted - 2006-03-06 : 02:25:46

Just a suggestion tightening up shallu1_gupta's code a bit:


--Copy new pensioners from Member to Pensioner
insert into Pensioners (<column names>)
select <columnnames> 
from  dbo.members AS M
where     datediff(year, dateofbirth, getdate()) >=60
      AND NOT EXISTS (SELECT * FROM dbo.Pensioners AS P WHERE P.ID = M.ID)

-- Mark any new pensioners as Inactive
UPDATE M
SET IsInactive = 1
FROM   dbo.members AS M
       JOIN dbo.Pensioners AS P
           ON P.ID = M.ID
where (M.IsInactive = 0 OR M.IsInactive IS NULL)

Kristen

kenone
Starting Member

14 Posts

Posted - 2006-03-06 : 09:14:47

Sorry I am still new to this vb.net and stored procedure thing...well I have already created the stored procedure using Query Analyser ..then how can I call it in vb.net(thru on click event)??? sorry for inconveniences caused...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 09:18:12

You should post the VB.NET related questions at .NET forums.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources