update a table using another set of data

Question

update a table using another set of data

sqlfriend

SSC Guru

Points: 52794
More actions
December 9, 2011 at 5:00 pm

#391094

In ssis package, I have first loaded a big table A with multiple columns, most populated, but only two are null columns that I need to populate from another set of data.
Then I uploaded a small table B from flat source file. I then have a derived conversion task to convert data columns.
Next I need to do an update from 2 columns of Table B to Table A by joining the primary key of the two table.
How can I do this step, use what task, thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

sqlfriends (12/9/2011)
In ssis package, I have first loaded a big table A with multiple columns, most populated, but only two are null columns that I need to populate from another set of data.
Then I uploaded a small table B from flat source file. I then have a derived conversion task to convert data columns.
Next I need to do an update from 2 columns of Table B to Table A by joining the primary key of the two table.
How can I do this step, use what task, thanks.

This may sound a bit odd, but here's how I'd perform this task:

-Upload Table B to whatever table you need to.

- Begin the import of Table A.

- During that import, use a lookup component off TableB and populate your two columns in the stream.

- Finalize a single insert to Table A.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks,

My table A has 400000 row counts, but the table B only has 100 row counts.

So I think if I should do a join of two tables to insert, or just update those are in table B.

Also when you say Finalize a single insert to Table A, what task should I use?

Thanks

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

sqlfriends (12/9/2011)
Thanks,
My table A has 400000 row counts, but the table B only has 100 row counts.
So I think if I should do a join of two tables to insert, or just update those are in table B.
Also when you say Finalize a single insert to Table A, what task should I use?
Thanks

Here's the general premise. If you do your 400,000 rows into TableA without the TableB data already associated, you have to do a 400,000 row insert, then a 400,000 row update.

However, if you use a lookup component in the stream, and bring in TableB FIRST, you only need to do a single insert to TableA afterwards. What you do is import TableB like you already do. Then you feed TableB into a lookup component in the DataFlow for TableA. Include/Replace the two columns that are currently coming through inaccurately or as NULLS. This way, when you're finished, you do a single insert and you're done with your data manipulation.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA