Thursday, March 22, 2012

Data Driven Query Task - question about Insert and Update queries

Does anyone know if, in an Update or Insert query within a DDQ task,
it's possible to have more than one statement in the query, and have
them both execute?
e.g.
update tablex set field1=1 where id=?
update tablex set field2 = 2 where id=?
Also, on the same subject, can you mix the statements, so let's say
even if it's the Update query you're using, could you tag an insert on
the end as well?
e.g.
update tablex set field1=1 where id=?
insert into tablex (field2) values (?)
Thanks for any help.Just as an afterthought, I suppose if this isn't possible it may be
useful to know whether the other two types of query can be 'hijacked'
and used as an extra insert (or whatever), but I'll wait to see if
anyone can confirm the original question or not before investigating
this one!!|||Quick note for anyone else looking at the same question: I've just
found the following on msdn:
"In order to refer to your SQL statements, you assign each statement a
name, called a query type. A query type, returned by your Microsoft=AE
ActiveX=AE script code, is used to select one of your SQL statements to
execute. Data Transformation Services (DTS) provides the following four
names:
Insert
Update
Delete
User
These query types should be viewed only as unique identifiers assigned
to each statement. It is in fact possible to perform any SQL operation
supported by the connection. It would be possible for example, to
perform four different updates, four different inserts or any mix of
these or stored procedures."
So I could do it by having an extra update as the 'delete' query, for
example. However, I'd feel it would be better 'practice' to have two
update statements within the Update query...I'd be grateful if anyone
with more experience than me could possibly provide an answer as to
whether you can have the two statements in the one query, as I've
trawled the internet and come up with nothing?
Many, many thanks indeed.,sql

No comments:

Post a Comment