|
I am building a web app that allows people to apply for jobs at my company. Everything that the user submits in the app is placed in a SQL 2005 database. One of the requirements is that all information in the applicant information is held for at least one year. In my main table which holds the primary id for the applicant I have an entry date field that is populated with the getDate() function whenever a new applicant is added to the system. Also, to keep from having duplicate records of an applicant in the system, the web app calls a stored procedure that deletes all of the applicant's info from the database based on the appID number (the month and day of the applicant's birthday combined with the last four of their SSN). Is there any kind of trigger or other SQL programming that I can put into place that will check the database daily and delete all of the applicant data that has been in the database for over a year?
|
|
|
|
|
While in the SQL Server Management Studio logged in as "sa", look under SQL server Agent and you will see a spot where you can schedule jobs on the server. Create a job that does your cleanup and schedule it when you want it to run and you're done. 
|
|
|
|
|
Okay, I can that, but how do I call my stored proc and loop through all of the apps that are over a year old? My Stored Proc is called DEL_App and it has a parameter of @appID, which is an integer. Now, I have created this select statement that will pull all of the apps that are over a year old:
select appID from applicant where Year(entryDate) < Year(getDate())
How can I loop through these apps and delete them using my stored proc?
|
|
|
|
|
Aptiva Dave wrote: select appID from applicant where Year(entryDate) < Year(getDate())
DANGER!! Deleting items that are returned by this select query will delete every app entered prior to the current year... while this seems like what you want, it's not.
For instance... say I enter 100 apps into your system on 31/12/2009. Then on 1/1/2010 the scheduled job runs... voila!! 100 apps that are only a day old are deleted!!!
|
|
|
|
|
hi,
you can use job schedule or else can put trigger on your applicant table which will fire after insert.
now use
select datediff(year,entryDate,getdate())
coz ur query will remove all records which are less than getdate()..
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Tripathi Swati wrote: put trigger on your applicant table which will fire after insert
Bad idea. What if it suddenly has to delete several thousand records? The delay will be significant. This sort of clean up should be done as a scheduled job, preferably out of hours and just after a backup, just in case anything goes wrong.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
yes, do agree that job scedule is better option but how come trigger will fire on delete also because i told trigger on after insert ???
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
The trigger will not fire on delete, but will attempt to delete records when it is fired by an insert. If there are lots of records to delete (i.e. old records) then there could be a significant delay
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Alright, how do I pull all of the appIDs from the select statement into the Stored Proc that I have already built to delete items? The stored proc only accepts one parameter, which is a single appID.
|
|
|
|
|
I am having problem to build a query. I have following data in a table.
NAME SUBJECT
------------------
sri physics
sri chemistry
sri math
amit physics
amit bio
amit math
sanjay Phsysics
sanjay chemistry
sanjay math
Ritu physics
Ritu Chemistry
Ritu bio
i need to find the name of student (engineering) who are studying physics, Chemistry and math. in above table output should be as follows:
NAME
--------
sri
sanjay
Can anyone tell the sql query.
Thanks,
Sri...
|
|
|
|
|
Try this
declare @t table(name varchar(50),subject varchar(50))
insert into @t
select 'sri','physics' union all
select 'sri','chemistry' union all
select 'sri','maths' union all
select 'amit','physics' union all
select 'amit','bio' union all
select 'amit','maths' union all
select 'sanjay','physics' union all
select 'sanjay','chemistry' union all
select 'sanjay','maths' union all
select 'Ritu','physics' union all
select 'Ritu','chemistry' union all
select 'Ritu','bio'
select name from @t
where subject ='maths' or subject ='physics' or subject ='chemistry'
group by name
having (COUNT(name)>2)
Output:
name
sanjay
sri
Niladri Biswas
|
|
|
|
|
Thanks Biswas,
Its working fine
Thanks,
Sri...
|
|
|
|
|
I have a Query
select sup_ref_no,mpr_no from qotemst where sup_ref_no ='103006'
Getting O/P like..... sup_ref_no mpr_no
103006 32
103006 32
Good. No problem. Also I have a Query
select mpr_no,mpr_date from mprmst where mpr_no ='32'
Getting O/P like..... mpr_no mpr_date
32 2009-10-15 00:00:00:000
32 2009-10-15 00:00:00:000
Good No problem. Also I have a Query
select sup_ref_no,mpr_no from qotemst where mpr_no ='32'
Getting O/P like..... sup_ref_no mpr_no
1551 32
1551 32
103006 32
103006 32
Good No problem. Also I have a Query
Select q.sup_ref_no,q.item_code,q.mpr_no,m.mpr_date from qotemst q,mprmst m where q.sup_ref_no='103006' and m.mpr_no=q.mpr_no
or
Select q.sup_ref_no,q.item_code,m.mpr_no,m.mpr_date from qotemst q,mprmst m where q.sup_ref_no='103006' and m.mpr_no=q.mpr_no
Getting O/P like.....sup_ref_no item_code mpr_no mpr_date
103006 SQUAWAS1 32 2009-10-15 00:00:00.000
103006 SQUAWAS2 32 2009-10-15 00:00:00.000
103006 SQUAWAS1 32 2009-10-15 00:00:00.000
103006 SQUAWAS2 32 2009-10-15 00:00:00.000
Doubling Problem, So how to solve it...
Any Ideas...Thanks
|
|
|
|
|
Try the queries like this
a) select distinct(sup_ref_no),mpr_no from qotemst where sup_ref_no ='103006'
b) select distinct(mpr_no),mpr_date from mprmst where mpr_no ='32'
c) select distinct(sup_ref_no),mpr_no from qotemst where mpr_no ='32'
then try the last
Hope it works
Niladri Biswas
|
|
|
|
|
|
You can nest the character replacements. It works by pattern matching so you can only use 1 character at a time. I have not tested with variable for keyword and replacechars but this works
Declare
@Var VARCHAR(100)
SET @Var = 'QWERTYUIOP'
--Replace(@Var, 'Q', 'X') Use this to replace @Var when nesting
SELECT Replace(Replace(Replace(@Var, 'Y', '_'), 'P', 'X'), 'Q', 'X')
|
|
|
|
|
Thanks for the answer but I made the solution using recursive CTE
But a 5 for u always
Niladri Biswas
|
|
|
|
|
please don't delete messages, we now have an answer without the question it belongs to. 
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Sounds like fun. A sort of Code Project Jeopardy.
|
|
|
|
|
hi
i try to install Oracle 10g on Windows 7 - but in the installation i get error:
"the procedure entry point GetProcessImageFileNameW could not be located in the dynamic library PSAPI.DLL. "
thank's for any help
|
|
|
|
|
|
Hi all,
I've started designing a document management system based on SQL Server and vb .net. I find this an interesting project, why? Document management systems have grown to be too complex. What are the main subjects to cover? I want to control the document flow through a product development project, then to the production cycle and maintenance. This means avoiding a catalog structure and always looking for the latest versions of CAD data, bill of materials and so on.
It would be nice to have some feedback, is this a good idea?
Best regards,
OAT
Norway
|
|
|
|
|
Wrong forum, you may get a more attentive audience here[^]. and possibly a more reasonable answer.
|
|
|
|
|
|
Hi i want export a table into database to a excel file
i search s command in Sql Server That help me Export a Table in Excel File without create file manully
please guide me
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
