December 9, 2010 at 5:34 am
Hi
I am looking for some advice or best practices on merging data from a separate system into a mssql database. Basically I am trickle feeding data from multiple Sybase databases into a single mssql db for a home grown "replication" number of sorts.
The tables that are getting replicated all have date created/modified columns and we are currently pulling in a rolling 2 days worth of data. These columns don't have a time component only the date (2 days is to catch any backdated trans).
The tables in mssql are a clone of their Sybase counterparts with the addition of a database_id column so we can have the same table from multiple Sybase databases replicated to a single mssql table.
So my top options on how to do this would be:
Sybase Table => transient mssql staging table => sql merge into prod table (updates/inserts)
Sybase Table => flat file output => sql merge into prod table using bulk openrowset (updates/inserts)
I would appreciate any comments on these or alternative approaches.
Cheers
December 9, 2010 at 11:01 am
Have you thought of using SSIS?
December 9, 2010 at 11:18 pm
Yes definitely these solutions would use SSIS to move data. I currently have option one working via SSIS. The merge is done using plain t-sql though.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply