Need help developing a large database upgrade process

Question

We have a database with ~ 100K business objects. Each object has about 40 properties that are stored among 15 tables. I have to get these objects, perform some conversions on them, and then write them to another database (with the same schema). This is ADO.Net 3.5, SQL Server 2005.

We have a library method for writing a single property. It determines which of the 15 tables the property belongs to creates and opens a connection, determines whether an existing property exists, and also inserts or updates accordingly and closes the connection.

My first pass in the program was to read an object from the source database, perform the conversion, and call the library procedure for each of its 40 properties to write the object to the destination database. Repeat 100,000 times. Obviously, this is clearly inefficient.

What are good designs to solve this problem?

thanks

Answer 1

This is exactly what SQL Server Integration Services (SSIS) is suitable for. It is documented in Books Online, just like SQL Server.

Answer 2

Unfortunately, I would say that you need to forget your client library and do all this in SQL.

Answer 3

? , , , . - , . , , , , SSIS, . , - , SQL.

, Create_Assembly, , SQL-. , SQL Server.

Answer 4

:

flatfile: flatfiles, grep, awk, sed, c, perl /

PRO: ; CON: ... , , , .

sql: sql, , , ' //, select.

PRO: , ; CON: . SQL SQL , .

t-sql , , sql aproach.

PRO: , CON: t-sql, - PL/SQL, .

(Java, #, VB...): -, . , , , ORM, , . NHibernate

ETL: , . . , , .

PRO: , - , , .

CON: , . : , SQL. , , IDE, , CI, , , .

PRO: , , IDE, Testing Frameworks, CI Systems, .

CON: ( , , . , , .

, -, , . , . , .

Answer 5

, SSIS - . , 30 , . t-sql, , . t-sql, (, 2000 ), , .