Archive tables in sql 2000 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Archive tables in sql 2000

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-09-01 : 08:10:51

Hi,
What are the areas needed to be taken care while archiving the
tables to another database?
Also,can we figure out the dependancy tables if the foreign key relation is not set since we need to move the child table along with parent?

gwilson67
Starting Member

42 Posts

Posted - 2011-09-01 : 10:07:45

sp_depends

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-09-02 : 00:35:19

Thanks. It seems to be returning the list of sps and views. Will it
give back the list of tables referred?

quote:
Originally posted by gwilson67

sp_depends

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:48:58

nope it wont show them. if you had set foreign key you could have used sysforeignkeys table but since you dont have it you cant use it either.
Do you atleast have some naming convention in place giving details of which column it relates to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-09-02 : 03:47:41

Let me put the question in another way.

There are two tables in current db-student and employee.
The objective is to archive the data in these tables-
Criteria is :
student table -before 1 year data
employee table -before 2 years data

The date field to be compared in the student table is field 'CreatedDate' and that of employee
is 'DOJ'
IN addition, I have kept a configuration table with field ConfigtableName ConfigColumnName , ConfigCutoffdate to store the above information.

a) How can I write a Generic query so that it dynamically take the tablename as well as column
name from the configuration table and insert the data to the archive dbs' tables?

SOmething like this....

INSERT INTO <ArchiveDb>.Dbo.<Table name obtained from config table>

SELECT
*

FROM
<CurrentDb>.Dbo.<Table name obtained from config table>
WHERE

<ConfigColumnName obtained from config table>
Less than <Cutoffdate obtained from config table>

b)Is it possible to loop each record one by one so that if an error occur in the nth iteration, it could save the error detail
of that record to a log?

quote:
Originally posted by visakh16

nope it wont show them. if you had set foreign key you could have used sysforeignkeys table but since you dont have it you cant use it either.
Do you atleast have some naming convention in place giving details of which column it relates to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 04:06:03

i dont think writing a generic sp based on dynamic tables is a good approach. why cant you use separate procs for each table archiving?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-09-02 : 04:27:59

IF we write separate procs, then if there are 10 tables, we need 10 procs right?

quote:
Originally posted by visakh16

i dont think writing a generic sp based on dynamic tables is a good approach. why cant you use separate procs for each table archiving?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 04:42:01

yep. we need. thats obvious rite. each table is a different entity with different structure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 07:36:55

We mechanically generate Sprocs for such things.

I write the first one by hand, and then make it into a SELECT statement:

SELECT 'CREATE PROCEDURE dbo.XX_SP_' + MyTableName + ' AS ...'
FROM dbo.MyConfigtableName

and so on

This is only really worthwhile if:

1) There are quite a few to create and/or there will be more in future
2) It is likely that you will change them in future - in which case you can regenerate the code and COMPARE with your current code, and merge the differences if you are happy with them (e.g. if you have additional, bespoke, code you can preserve that during the "merge")

The other way I do it, where the numbers are small, is to create a template. So I have


-- Replace parameters:
-- MyTableName -- the name of the table
-- YYYY -- Some column name

CREATE PROCEDURE dbo.XX_SP_MyTableName
AS ...
SELECT YYYY FROM dbo.MyTableName
...

and then I use the comments at the top as a basis for all the things I must Find&Replace.

This is preferable to "just finding an existing one and using that as a template" for three reasons.

Firstly the template is always the latest version. If you just choose an existing one you may be carrying forwards some stale legacy code too.

Secondly, you can put useful instructions in the comments at the top indicating exactly what the replace-parameters are for

and Thirdly you can use replace-parameter names that are absolutely unique within the code. If you take an old piece of code and do Find&Replace on that you may have ambiguity between Old / New names, partial matches, and so on, which then give you a bunch of bugs to sort out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources