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.
| Author |
Topic |
|
wonky donkey
Starting Member
15 Posts |
 Posted - 2007-06-28 : 10:31:46
|
| Hi GuysJust wondered if someone could point me in the right direction with this. I have a table (e.g table 1) which is a copy and has been generated and placed on a server from a flat file database source. However, changes will be made to this database during the week (not on the server) and once a week i want to be able to create table 1 on the server each week. Now, instead of just copying the table once a week which will capture all of the changes, i want to be able to only update any of the data thats been changed.Is the answer here in the update function? i've used this before but only to set new values, which in this case i dont know what they will be so it doesnt seem to be the way to go. Apologies if this is a bit of a basic problem, i'm a novice when it comes to programming!cheers |
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-28 : 10:44:12
|
quote:
Originally posted by wonky donkey
Hi GuysJust wondered if someone could point me in the right direction with this. I have a table (e.g table 1) which is a copy and has been generated and placed on a server from a flat file database source. However, changes will be made to this database during the week (not on the server) and once a week i want to be able to create table 1 on the server each week. Now, instead of just copying the table once a week which will capture all of the changes, i want to be able to only update any of the data thats been changed.Is the answer here in the update function? i've used this before but only to set new values, which in this case i dont know what they will be so it doesnt seem to be the way to go. Apologies if this is a bit of a basic problem, i'm a novice when it comes to programming!cheers
Read BOL : replication and log shipping. |
 |
|
|
wonky donkey
Starting Member
15 Posts |
Posted - 2007-06-28 : 11:03:42
|
| Hi, thanks for that. It seems quite advanced for what i'm trying ot do though. All i'm doing is uploading a database table once a week to a server. During that week some changes will have been made to that table but due to its size i'm trying to just overwrite the data which is different. Is there not a fucntion within the upload i can call on to check the data and if there is a difference then update the table with the changed data?Apologies if that isnt clear. |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-06-28 : 13:05:50
|
| Without information about your data (table schema and sample data), how would you expect anyone to be able to help you? My crystal ball says "not enough information", I'm afraid.Your question is similar to calling your auto repair shop and saying "My car doesn't work. Can you fix it and how much will it cost?" The mechanic is going to need a whole lot more information than that in order to give you an answer. |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-28 : 13:35:05
|
quote:
Originally posted by KenW
Without information about your data (table schema and sample data), how would you expect anyone to be able to help you? My crystal ball says "not enough information", I'm afraid.Your question is similar to calling your auto repair shop and saying "My car doesn't work. Can you fix it and how much will it cost?" The mechanic is going to need a whole lot more information than that in order to give you an answer.
What are you talking about???? He gave plenty of information. He's doing an upload of a table, and only wants to move the changes. |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-28 : 13:37:26
|
quote:
Originally posted by wonky donkey
Hi, thanks for that. It seems quite advanced for what i'm trying ot do though. All i'm doing is uploading a database table once a week to a server. During that week some changes will have been made to that table but due to its size i'm trying to just overwrite the data which is different. Is there not a fucntion within the upload i can call on to check the data and if there is a difference then update the table with the changed data?Apologies if that isnt clear.
Nope. Trying to write this by hand would be a pain too. You'd be better off just moving the whole table. This seriously sounds like something that would be best served with replication. It would do everything you are asking, and would be much easier than writing something yourself. |
|
|
|
wonky donkey
Starting Member
15 Posts |
Posted - 2007-06-29 : 05:31:21
|
| Hi, thanks very much, i'll go back to looking into replication, it seems the general consensus on here as to the best way of tackling this.Oh and KenW, get a life. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-29 : 09:00:57
|
| Have a Staging table in a database local to where the import Flat File data is. Initially populate this from the Flat File.Import a new copy of the "flat file" into a temporary table once a week.Update the Staging Table selectively by comparing with the Temporary Table. (i.e. use a massive WHERE clause to only update rows that are different. [We generate the WHERE clause mechanically so that we can easily rebuild it if the schema of the table changes]).Also Insert new rows and Flag rows in the Staging table which are no longer in the Temp table's data.As part of this you need two extra columns in your Staging table:Update date/time and Action. Set the Action to "D" if the row is deleted, something else for Insert/Update.Now you can pump the changes-only to the Staging table to a remote server. This will only copy rows that have changed, so will be a "skinny" transfer. You could use replication for this, but for what is basically a Batch operation I wouldn't bother.Doing this manually you would: have an identical Staging Table at the remote end; transfer anything with an update Date/Time greater than the MAX value at the Remote end.So now you have a Staging Table at the Remote end that is up to date. Maybe this is all you need, or maybe you then need to selectively update some "production tables" at the Remote end.For example, we use this process to get data from 3rd party systems - perhaps an order Fulfilment system in the Warehouse to an eCommerce Website's database; we have a very small data pipe between the Warehouse and the ISP, so want to transfer the minimum amount of data.The Staging tables we use are in the same "shape" as the original database - i.e. the same tables & columns. However, we ONLY include columns that we are ultimately interested in - so oftentimes when we get the new "flat file" there are rows that have changed, but not in columns that we are interested in, so those don't get imported, and do not change the Update Date/Time.The data gets synchronised on the remote server. Still in the "shape" of the 3rd party package. Then, once it has arrived at the Remote database, we run a series of Updates to freshen up the data in OUR application - i.e. by updating our tables JOINed to the Staging tables.This has proven to be quite robust - the pull from Flat File to local server can happen as often as it needs to. If the COMMs is down to the Remote Server then that bit doesn't run until the Comms is back upright again.If a server is rebooted in the middle of any of these operations it doesn't matter (although you do need to ensure that all rows that are needed to update a Staging table are imported in one transaction sot hat the "Max Update Date/Time" is always valid).Note that by keeping the data in the same "shape" as the 3rd party application all the way to the Remote Database we can change the "Import Logic" at the Remote database, if we need to. If we changed the data into "our shape" at the initial import and then needed to change the Import Logic we would potentially have to then re-transfer all the data across the slow link.Kristen |
|
|
|
|
|
|
|
|
Updating a table with only changes