Appending Data from SQL to SQL Database 1

[Schnappa]

Hi all

Having been thrown in the deep end and told to organise a SQL server service to run databases previously held in Access, I am having some problems with appending a table for one month to the next.

We currently run a monthly dump of data from a proprietory third source into Access. We then use Crystal to report against this database (my actual job!!). At present transactional history is kept in one table, and each month a new Transaction table is created. To get month Vs month data we presently run a crystal to look at the seperate databases.

SQL was thought of as a solution, where I could house the reformed access databases via DTS in the SQL environment. The problem of course, is that I would prefer to be able to 'combine' the transaction (and other) tables to have a larger table containing 1+ months worth of data.

I have tried to use DTS for this, without a lot of success. I am aware that we use a unique field called TranNumber which is reset each month. Therefore if I am combining the two, DTS may have an issue with this fact.

Can anyone at least let me know if I am on the right track - or completely off course. Of course, the next thing is that any indexing inherent in Access are lost using DTS...but maybe that is another post.

Cheers
Gez

 

[JayKusch]

OK ... I'll give it a first shot.

For your MonthlyData(MD) table that you are creating in SQL Server(SS), create the schema, table layout, based on how it is structured in Access.

I would however create another field, Integer, in the MD table, maybe called ArchiveMonth(AM) or something to that effect.

In this I would load the Month and Year that you are loading at that time. So if you were loading May of 2004, all the archive records for that may push would populate the AM field w/ 200405. Then you can group the same TranNumbers for a given month based on the AM field.

You are also able to query the table based on the AM number for multiple months as in ...

SELECT *
FROM MonthlyData
WHERE ArchiveMonth BETWEEN 200401 AND 200407

Now you need to probably index the AM field for better use. This is also the time to create the other indexes that are in your Access DB tables which are not reflected in your SS DB tables. I do not beleive index transfer from Access to SS.

Once that is in place, you should be able to create a DTS package, or a SQL script, to load each months worth of Access transactions into you SS MonthlyData table. Just remember to add a placeholder and appropriate year/month value to your export to populate the ArchiveMonth field you created in the MD table.

Hope this gets you going!

Thanks

J. Kusch