Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-09-28 : 14:57:45
|
| Thank you to those who tried to help me with this yesterday, but i think i fell short in describing what i'm trying to do. Basically, we take data from customer's old db's, regardless of what they are/were using, and move that data into a new db structure of ours that they have purchased. In this case, i have some excel sheets from the customer with data from their old db. As we're moving it into a SQL 2008 environment, here is the dilemna with one set of fields. In the customer's excel sheet, there is ONE column (Skin_Tone) for this example which contains one row per person that identifies their skin tone as either LGT, MED, DRK, or FAR. In the new db, the skin tones are identified via bit fields, one for each possible skin tone. In moving the whole of the rest of the data over, i'm simply using a query that starts with Insert Into, listing the column names in our db, followed by the values in the excel sheet. One "Insert Into" statement for each row. What would be the best way to translate the single value for the Skin_Tone field in the excel sheet into the multiple possible columns in the new db? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 15:03:48
|
| if bit fields are named LGT, MED, DRK, FAR etc in table the insert will be likeINSERT INTO Table(...other columns,LGT, MED, DRK, FAR,....)VALUES(...other values, CASE WHEN Skin_Tone ='LGT' then 1 else 0 end,CASE WHEN Skin_Tone ='MED' then 1 else 0 end,CASE WHEN Skin_Tone ='DRK' then 1 else 0 end,CASE WHEN Skin_Tone ='FAR' then 1 else 0 end,....)if you're building this out of excel then you need to replace column names with correponding cell names------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-09-28 : 16:15:57
|
| Awesome. Thank you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 22:50:15
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Reiterating question from yesterday.