Link or replicate

[djj55]

Hello, SQL2008R2
We just installed a new phone system. The system has its own server and SQL2008R2 instance. What I need to do is keep a table on SQLPhone up to date with information from SQLProduction also a SQL2008R2 instance. The table is used by the phone system to route calls based on the account.

Note that SQLProduction has a link to SQLPhone but SQLPhone does not have any linked servers for security reasons.

I have a procedure that gathers the information needed from SQLProduction into one table once every night. If I rebuild the table on SQLPhone by deleting the contents then inserting data, it takes quite some time as there are two million records to move.
Example:

DELETE FROM [instance].database.dbo.table; 

INSERT INTO [instance].database.dbo.table (
    IDX
    , AcctNum 
    , Extention 
    , Workgroup 
)
SELECT 
    IDX
    , AcctNum 
    , Extention 
    , Workgroup 
FROM #PhoneData 
ORDER BY 1 
;

Would it be better to store the data to a table that is replicated to the phone server? What about using a MERGE update/insert? The MERGE I know has some caveats.

Thanks for any suggestions,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

two million records to move

Of those 2 million records, how many of them actually change from day to day? I'm betting that it's a relatively small percentage.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

djj55,
When working with disconnected systems like this I prefer a granular wholesale replace. Use the triggers that track the update to do/queue the update to the other table.
I don't know how intense your data is but you may only want to queue them, and process them at night.

With your data you're probably fine running a
If Exists() delete
insert

HTH,
Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[djj55]

George, thus the MERGE question.
Lodlaiden, yes nightly is the preferred method.

Between both responses I had an idea. I could update a "local" table getting the OUTPUT and then update the "remote" table for only the affected records. Also with the INSERT of new records insert into both tables. As the agent and workgroup may change for a given account it does not happen that often.

Thank you for your replies,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

As a follow-up.
The MERGE will not work with a linked server as
[tab]Msg 5315, Level 16, State 1, Line 2
[tab]The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.

Locally the MERGE works great.

MERGE MyTable AS Target 
USING (SELECT IDX, AcctNum, Extention, Workgroup FROM TempTable) AS Source 
ON (Target.AcctNum = Source.AcctNum) 
WHEN MATCHED THEN 
    UPDATE 
        SET Extention = Source.Extention, 
            Workgroup = Source.Workgroup 
WHEN NOT MATCHED BY Target THEN 
    INSERT (IDX, AcctNum, Extention, Workgroup) 
    VALUES (Source.IDX, Source.AcctNum, Source.Extention, Source.Workgroup) 
OUTPUT $action, Inserted.*, Deleted.*
;

Still thinking about the replication.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!