Please Help..new to SQL

[associate72]

Please help.
1) What would be the most efficient way (command/code) to move records which are older than 9 months from Table A to Table B on a regular basis?

2) Does indexing tables on a regular basis during off-peak hours improve overall performance? And if so, how would I go about scheduling this to occur?

I appreciate any help/ insight to my questions.

Thanks in advance.

 

[gk180662]

Hi,
about 1) tray it:

====
insert into T_B (p1,...,pk,...,pn)
select p1,...,pk,...,pn from T_A where <exp1>
====

the condition <exp1> f.e.: T_A.dtDate < :dtStart9Mth

 

[AtomicWedgie]

and after the insert you must delete the records from table A that you inserted into table B or else you are only copying the records and not moving

delete * from T_A where <T_A.unique_id> in (select <T_B. unique_id> from T_B)

 

[JamesLean]

1) Create a procedure to archive the data and schedule it using a SQL Agent job.

CREATE PROC apArchiveData
AS

SET NOCOUNT ON

--copy data to archive table
INSERT tblb (<col_list>)
SELECT <col_list>
FROM tbla
WHERE date_col < DATEADD(mm, -9, getdate())

--delete data from main table
DELETE tbla
WHERE date_col < DATEADD(mm, -9, getdate())
GO

2) It is often a good idea to rebuild indexes from time to time to keep performance levels high. Again, schedule an Agent job to run DBCC DBREINDEX commands against commonly updated tables. You can find syntax details and info on this in Books Online.

Also, if you are unfamiliar with SQL Agent then look this up in BOL too.

--James