July 24, 2009 at 7:49 am
Hi,
I apologize for the 'baby' style example but I'm trying to figure out the best method for a bigger problem.
I've found one of the most useful purposes of having a Database computer system is to prevent people having to capture the same information again and again. So for example I have a program that stores and reports on Baseball stats for a Ball club. There are three systems involved all together:
1. Registration (At a club)
2. Statistics (At head office)
3. Regional (At head office)
So on the Registration system all the players are captured with all the usual stuff like Identification No, Surname, Gender, Address, Region etc.
They make their SQL database open to me to access cause the Statistic System needs to know who is playing this year but I still have the players from last year so I can't to a straight Append all I'd have to test first if that player exists. The other thing is that I also have to check the address and Region in case the player has moved and Update that. So basically it is an Upsert.
My question is: What is the most optimal way to do this? Is is better to copy the table (50 records) compare and do the Update and inserts? Can you compare over the network? Is it a good idea to link the servers? I'd have to run the import once a week to get changes etc can I use a SSIS package and schedule it with SQL server agent?
Regards
SAWolf
July 24, 2009 at 8:17 am
I dont think i understood your requirement fully.
I guess u want to insert some records in a table which is already having records from the past year. In this case you can add a column to the table "YEAR" denoting which year a particular player played. This way you can always query the table to find the players for current year.
Also i didnt understand,
They make their SQL database open to me to access
Do u mean to say each player have their own sql database which u connect remotely or what?
July 24, 2009 at 8:35 am
For 50 rows, I'd use SSIS and copy the table each week and then do the upsert.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply