Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-05-13 : 08:17:58
|
| Daniel writes "HiI have a challenge transforming SQL Server databases:I've designed a fairly complex relational database for my application. The database is going to be populated from data from external sources. I have no control over the design of the external data, although it does have a fixed design.So I want to write some data transformation routines using VB to go thru the external data (which I've imported without modification into its own SQL Server database) and write it to my database. The data transformation can involve quite complex processes; for example, for any one row from the external data, I want to manipulate the data in several columns (string manipulation, string to date conversion, etc); then check the existence of certain rows in different tables in my database, then insert or update several rows in several different tables.I've looked at using the VBA functionality within SQL Server DTS but it appears to apply only when you are going from one table to one table. I've looked at some books (for example, SQL Server 2000 with MS VB.NET by Rick Dobson) but I've found little useful information in there.Can you give me some advice? Is using VB .NET the best option? Or should I be doing it with long and complicated T-SQL statements in a sproc? Either way, how do I actually do it? My background is in traditional programming, where I would read one row, then do all the processing on that row, then read the next row and so on. Most of what I read about SQL Server suggests that that kind of processing is inefficient.I'm using SQL Server 2000 Service Pack 3a, Visual Studio .NET 2003, running on Windows Server 2003 Enterprise edition." |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-13 : 13:51:24
|
| Doing this on a row-by-row basis in VB.net will probably take some time. What you might want to do is this. Divide this transformation up into steps.1. Using T-SQL Do your string manipulation and data type conversions. You may want to move data from your "dirty source" table into a new set of "clean data" tables. 2. Now that you have clean data to work with, you can do all of the checking and inserting as needed in either T-SQL (if you can) or VB.net. The T-SQL way will be much faster if you don't use a cursor.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
|
|
|
|
|
Complex Data Transformation using VB .NET