June 1, 2009 at 1:36 pm
Most of the destination columns will need to be hard coded. Default values on the table object are not possible. Is there no such object that will provide a zero value, AND avail itself after being assigned?
What am I forgetting here? How else can I skin this cat? Ole DB source object with a hard SELECT, as in...
SELECT
1 as hard_one,
0 as hard_zero,
-1 as hard_negone
UNION
SELECT
1,
0,
-1
UNION ... -- (as many times as needed) just seems clunky as heck
I found another thread that poses this issue. The thread seems to have settled on "you can't do that", but I'm skeptical...
http://www.sqlservercentral.com/Forums/Topic583781-148-1.aspx
thanks in advance SSC!
June 1, 2009 at 1:42 pm
Could you put what you are attempting to accomplish into some context? The SQL you provided will only have 3 rows returneded. The union will eliminate duplicates.
June 1, 2009 at 1:57 pm
sorry Lynn. "UNION ALL" would have the desired effect in that example, but it seems too contrived to be elegant much less efficient.
Destination table has 150 columns.
There is a single source table that will provide values for about 20 of those 150 columns.
The other 130 columns need to be set to 1, 0, or -1.
It seems silly to derive 130 hard coded columns for only 3 different values.
Thanks!
June 1, 2009 at 2:05 pm
Nope, not helping. Still don't see what you are trying to accomplish. You really need to SHOW ME what you are trying to accomplish, not just tell me. I am a visual type person and need to see the problem as well.
June 1, 2009 at 4:32 pm
Greg J (6/1/2009)
sorry Lynn. "UNION ALL" would have the desired effect in that example, but it seems too contrived to be elegant much less efficient.Destination table has 150 columns.
There is a single source table that will provide values for about 20 of those 150 columns.
The other 130 columns need to be set to 1, 0, or -1.
It seems silly to derive 130 hard coded columns for only 3 different values.
Thanks!
Are the various -1s, 0s and 1s fixed for each record? If not, where is this data coming from?
If they aren't, it would be interesting to know what these fields represent. If they are always set to the same number, why not just add default values for them at the table level?
Or could you run the import of the 20 columns and then run a single UPDATE query afterwards to fix the other 130?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply