November 29, 2011 at 5:18 pm
I need help in figuring this out and I will try my best to describe the problem.
I have two tables: States 1 and States 2
States 1 has two columns called 'API' and 'Abbreviation' and data in those two columns.
States 2 has the same two columns but only the 'Abbreviation' column is filled up.
I need a way to Lookup and match the abbreviations (50 states) and insert the appropriate 'API' numbers from States 1 table to States 2 table.
Sounds easy, I know. But, how do you do it without having any 3rd 'Temporary States' table. Does Lookup support matching and then inserting data into a single column?
* Of course there are more columns then this, but I simplified it as much as possible.
November 29, 2011 at 5:32 pm
Not exactly, and without more columns I'm slightly confused. This sounds like an update statement, something you try to avoid in SSIS. If you were adding or transforming a column for insertion to a staging table I'd do this.
If you're updating a persisted table, I'd personally stick with T-SQL. Pull the short table over via a linked server if you must, or dump it into a local staging table.
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
November 30, 2011 at 10:21 am
Well sorry if it was confusing, but the other column is state name.
So its like this:
States1 TABLE
ID API ABBREVIATION
1 1 AL
2 2 TX
3 3 CA
States 2 TABLE
ID API ABBREVIATION
1 TX
2 CA
3 AL
What I want to do is a lookup, that matches on ABBREVIATION, and then inserts the API number from STATES1 Table, to STATES 2 Table, without creating a staging table or anything.
Is there a way for SSIS to do a simple lookup and then insert the correct numbers into the API column?
Once I know how to do this, I can work on the ZIPCODES table which is like 50,000+ data.
Hope this clears things up.
November 30, 2011 at 10:25 am
Doh, the table doesnt look like I wanted it to. Also this is for 2008 SSIS
November 30, 2011 at 12:26 pm
The only way you can do it is to use the SQL Command option and update the rows one at a time. That's a really ugly solution though, especially if you have a lot of rows.
December 5, 2011 at 10:21 pm
You can use Execute SQL Task in control flow to do a bulk update which is faster than doing a row-by-row update. You can write an update statement joining the two tables for the bulk update.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply