|
|
Posted by Erland Sommarskog on 04/25/06 00:29
(nicolas.bouchard@gmail.com) writes:
> I am developing an integration process between two databases. One of
> them is a SQL Server 2000 and the other is using MSDE 2000. The
> integration process is done in C# (VS2003).
>
> The main database is the SQL Server, the MSDE will contain a really
> small subset of the data found on the main. To help diminish the amount
> of time taken to develop an integration process between those
> databases, the same structure are found on both side. The only
> difference, when I insert data in the MSDE from the SQL Server, I set
> the IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.
The first reaction is: have you considered replication?
The second reaction is: skip IDENTITY, and generate the IDs on your
own. That is a trivial business. It's only if you have a high INSERT
rate from simultaneous processes that you need IDENTITY, as rolling
your own key can result in contention issues.
IDENTITY is convenient at times, but not in a situation like this. It
causes more problems than it solves.
Yet, an alternative is to use bulk load to extract and load data.
Moving one at time as you do not is not terribly effecient. Which may
not matter if the data size is moderate, but for something like
10-20 MB it could. With BCP it's easy to insert explicit IDENTITY
values with the -E option.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|