Best way to do...

[jacob94]

What is the best way to do the following:

tblLive
code
1
2
3
4

I need to copy and append tblLive to tblSaved. tblLive will keep incrementing data with unique keys in which I have to also added to the tblSaved table.

I was thinking I need to mark the records I get initially and only go after differences there after. I wanted to get some thoughts on this, most efficient way...

 

[AlexCuse]

Your table is not going to do anything, your server is ;-)

I think you want to first create a table (tblSaved) that is identical to tblLive except in name. Then you have a few options, depending on how 'live' you want tblsaved to be.

To insert several new records at a time, try this:

insert into tblSaved (field1, field2, field3...)
select field1, field2, field3
from tblLive left join tblSaved 
on tblLive.[PRIMARY KEY FIELD] = tblSaved.[PRIMARY KEY FIELD]
where tblSaved.[PRIMARY KEY FIELD] is null

To do it as they are added, one at a time, look into using a trigger, or simply modifying your app to append to both tables.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jacob94]

I do have intentions of using a trigger once I get this part down. Thanks for a quick response. I had this in mind as I used it before for another app, but I was worried about the load on the sql servers. I am using a linked server, trigger on one side, firing this SP on another server. Are their other approaches or do you think this is the best way. I never want to waste processing time on records I have in my tblSaved.

 

[AlexCuse]

A trigger should work pretty well, although I am not speaking from experience here. If you have a lot of activity and don't need your saved table to be 100% up to date, you may consider setting up something to run every hour or so using a query like I specified above. This would reduce your updates to 1 per (time interval) rather than once for each time a record is added.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jacob94]

I need it to run after each insert or update but I am concerned about the load...

Any other thoughts?

How does the trigger queue up requests if they happen while the previous is still executing?

Dist. Transactions Cordinator?

 

[AlexCuse]

If you need it to run after each insert/update, you will actually need two triggers I believe (one insert and one update). I understand your worry about the load, but I don't htink you have any choice really here (other than configuring your app to work with both servers but that would not really be a very good practice). Because you will only be moving one row at a time I don't think you need to worry about it too much.

As far as how the trigger will queue requests, you said that it your trigger is used to execute a stored procedure on the other server. I think it would depend on how your proc is set up.

I would say that others will probably chime in with more advice, but your subject is not very descriptive. Check out this FAQs for posting advice. Particularly #9.

faq183-874

There also might be a FAQs about triggers, although I didn't see any.





Ignorance of certain subjects is a great part of wisdom