Looking for a SqlBulkCopy that can do UPDATE...

[TheSteph]

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.

 

[Marc Gravell]

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

 

[Ignacio Machin ( .NET/ C# MVP )]

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

 

[TheSteph]

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.


message

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

 

[Ignacio Machin ( .NET/ C# MVP )]

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

 

[Ignacio Machin ( .NET/ C# MVP )]

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