Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Using Lookup Queries to Modify Data

SQL Server 2000

Lookup queries are not limited solely to SELECT statements. INSERT, DELETE, and UPDATE statements, as well as stored procedure invocations, all can appear in lookup queries.

The UpdateEmployee query updates a value in the Employee table when you provide it an EmployeeID:

UPDATE Employee SET HasTakenVacation = 0 WHERE EmployeeID = ?

Execute this query for every row in the source rowset with the following Microsoft® ActiveX® script code:

DTSLookups("UpdateEmployee").Execute(DTSSource("EmployeeID"))

Note  An INSERT query can be used in conjunction with a Transform Data task to split a source rowset, sending rows to two different tables. However, when both tables reside in Microsoft SQL Server™ databases, this practice results in longer execution times than sequentially running two Transform Data tasks with fast load enabled.

See Also

Using ActiveX Scripts in DTS

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.