Michael Otey's editorial "ADO.NET Loopholes" (October 2001, InstantDoc ID 22132) begins, "With the imminent release of Windows .NET Server, we'll soon see deployment of applications built on the Microsoft .NET Framework." This statement could confuse some people about what a .NET application is versus what Windows .NET Server is. The source of this confusion is Microsoft's marketing department's penchant for naming every new technology ".NET." And Microsoft's competitors have promoted the false idea that you need Microsoft's latest OS to run its latest applications. But I've been deploying .NET applications, of both the Windows and Web variety, on Windows XP, Windows 2000, and Windows NT for a year now. All you need is the .NET runtime—freely downloadable from Microsoft. If people are waiting to buy Windows .NET Server before they develop and deploy .NET applications, they don't understand what they're missing.
I didn't mean to imply that Windows .NET Server is required for .NET applications, only that its availability will encourage businesses to build those applications. You're right about the confusion surrounding the .NET name; perhaps Microsoft will clear up this confusion in future products.
Set-Based Solution Gives Better Performance
I have a question related to Itzik Ben-Gan's "DTS and the Data Warehouse" (August 2002, InstantDoc ID 25544). I'm using data-driven queries to add a record from my source AS/400 system to my SQL Server database if the record doesn't already exist or to update the record from the source system to SQL Server if the record is already there. In my source file, I don't have a mode-type field that tells me whether to add the record to the destination database or update it. Can I accomplish this goal through the Data Driven Query (DDQ) task or any other Data Transformation Services (DTS) task?
If the only way you can tell whether you need to use an INSERT or an UPDATE statement is to query the target table to see whether the source table's key exists there, consider the following approach instead of using the DDQ task. First, copy the source data to a staging table in the target database. You can do this by using a regular data pump task or an Execute SQL task that copies the data from a linked server. Then, use the following queries to update existing data or insert new data, respectively:
UPDATE T SET data_col1 = S.data_col1, data_col2 = S.data_col2, ... FROM Target AS T JOIN Stage AS S ON T.key = S.key INSERT INTO Target SELECT * FROM Stage WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.Key = Stage.Key)
Overall, such set-based operations usually provide better performance than the row-by-row processing that the DDQ task uses.
