By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,152 Members | 2,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,152 IT Pros & Developers. It's quick & easy.

Looking for a SqlBulkCopy that can do UPDATE...

P: n/a
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.
Apr 2 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Apr 2 '08 #2

P: n/a
On Apr 2, 9:52*am, "TheSteph" <TheSt...@NoSpam.comwrote:
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.
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
Apr 2 '08 #3

P: n/a
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.
"Ignacio Machin ( .NET/ C# MVP )" <ig************@gmail.comwrote in
message
news:2a**********************************@d45g2000 hsc.googlegroups.com...
On Apr 2, 9:52 am, "TheSteph" <TheSt...@NoSpam.comwrote:
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.
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
Apr 2 '08 #4

P: n/a
On Apr 2, 11:50*am, "TheSteph" <TheSt...@NoSpam.comwrote:
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.
Hi,
Thanks for the tip !!!!
I will look into it
Apr 2 '08 #5

P: n/a
On Apr 2, 11:50*am, "TheSteph" <TheSt...@NoSpam.comwrote:
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.
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
Apr 2 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.