How To Update data?

I thought there would be an easy way to do this ... is there?

I have source data (resulting from some conversions and derives and other operations) which I want to update into a destination table based on a lookup.

So, on lookup success how can I update some columns in that row?

TIA

[290 byte] By [AzureBell] at [2007-12-17]

«« Can WWF Be Hosted on Linux?
»» dll file in exe.file

# 1

Use the OLE DB Command component with an update statement.

You could use the SCD that would enable inserts to be done as well if rows aren't found.

SimonSa at 2007-9-9 >

top of Msdn Tech,SQL Server,SQL Server Integration Services...

# 2

Hi Simon,

I had thought of that but could not think how do it ... in the Update statement how do I access the row columns that I am updating from?

ie update tablename set colname = ? where id = ? (how do I fill in the ?s?)
What is the SCD?

Thanks
Mark

AzureBell at 2007-9-9 >

# 3

You put ?s in the sql statement and then use the Column mapping to map each column in the flow to the relevant parameter, (one ? for each parameter)

SimonSa at 2007-9-9 >

# 4

Aaahhh - it was prophetic therefore that I used ?s in my message. Many thanks!

AzureBell at 2007-9-9 >

# 5

Just a question - using the Oledb command to update each row is quite slow - is there no faster / better way to update data?

Thanks,

Nicole

NicoleM at 2007-9-9 >

# 6

Ash Sharma has an excellent post on the subject here: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03/10/8732.aspx

-Jamie

JamieThomson at 2007-9-9 >

# 7

Thanks!

NicoleM at 2007-9-9 >

SQL Server

SQL Server Integration Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech