 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / April 2008Looking for a SqlBulkCopy that can do UPDATE...
Using : MS-SQL 2005 / .NET 2.0 / VS2005 I have two server with the same database def but different data. I would like to import data from Server2.TableA to Server1.TableA. If a row doesn't exists on Server1.TableA, it should be added; if the data already exists it should be updated. I have to write a C# application do this operation on a daily basis on about 50 different tables. - SqlBulkCopy : doesn't works because it can only do INSERT. - DataTable.Load() : doesn't works because the destination table should be entirely loaded into memory and it's too much data with some table. I'm looking for something like SqlBulkCopy but it should handle the UDPATE. Does anybody have a simple solution to this problem ? Thanks for your help ! Steph. A staging table... use SqlBulkCopy to throw the data into the twin, then use an SP to do the merge. In SQL Server 2008 there is also a MERGE syntax for exactly this case, but until then you'll need an INSERT (where not exists), an UPDATE (inner join) and a DELETE (where not exists, other way around). Marc > Using : MS-SQL 2005 / .NET 2.0 / VS2005 > [quoted text clipped - 19 lines] > > Steph. I have something similar to your escenario and this is what I do. I do use DTS , not sqlbulk, to do the copying. I do the copying to a temp table (staging table) in the DB The last step in the app (after executing the DTS) is to execute a SP that insert/update/delete the real table based on the imported table. The only problem with that solution though, is that SQL Express 2005 does not support DTS so I have to use MSDE Thanks for your advices ! I read that you don't have DTS in you SQL Express 2005. but in fact there is a [hidden] way to have DTS in SQL Express 2005 : Just Download SQL Express 2005 "TOOLKIT Edition" on MS Web site (the file is : SQLEXPR_TOOLKIT.EXE). Install it on your computer. The DTS Wizard will be installed in this path : C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe You can easily add it in the Tool menu of the SQL Management Studio Express. Steph. On Apr 2, 9:52 am, "TheSteph" <TheSt...@NoSpam.com> wrote: > Using : MS-SQL 2005 / .NET 2.0 / VS2005 > [quoted text clipped - 21 lines] > > Steph. I have something similar to your escenario and this is what I do. I do use DTS , not sqlbulk, to do the copying. I do the copying to a temp table (staging table) in the DB The last step in the app (after executing the DTS) is to execute a SP that insert/update/delete the real table based on the imported table. The only problem with that solution though, is that SQL Express 2005 does not support DTS so I have to use MSDE > Thanks for your advices ! > [quoted text clipped - 8 lines] > You can easily add it in the Tool menu of the SQL Management Studio > Express. Hi, Thanks for the tip !!!! I will look into it > Thanks for your advices ! > [quoted text clipped - 10 lines] > > Steph. I just found it and I'm downloading as we speak, I also found the existence of SQL Express with Advanced Services, I had no idea such a product was available, wonder if it's a public fact that they are available? Did anybody hear about it? Or maybe it was available from the beggining and I had no idea Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.