November 1, 2010 at 2:35 am
I need to constantly merge (upsert/delete) data from an ODBC data source to a SQL Server 2008 database (number of rows vary from one row to 100000 of rows)
What would you recommend as the most efficient approach (using .net 3.5 ):
1.Use SqlBulkCopy into temp table then call stored procedure with Merge command using temp table as source.
2.Calling a Stored procedure that has a table value parameter, where data is sent as a parameter (SqlDbType.Structured), table parameter used as source of merge command. Is the data sent via table parameter sent to server in a bulk operation? Is it possible and efficient to use it in cases where there are > 1000 rows?
3.Call stored procedure with merge command that uses OpenRowset bulk to get the data from the ODBC source (use linked server?)
4.Any other way.
Thank you!
November 1, 2010 at 10:53 am
My personal preference is a staging or temporary table to drop the load into. The reason for this is that at any specific point of failure you don't have to repeat prior steps to troubleshoot. It also allows for an easy place to do transformations and data cleansing, rather then trying to do it on the fly in the transformation dataset in SSIS.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 1:28 pm
I would concur with Craig's advice.
The probability of survival is inversely proportional to the angle of arrival.
November 2, 2010 at 2:47 pm
Thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply