how do I populate one table from another and concatenate fields in the process?
We changed the structure of a key table in our database. Information that used to be stored in multiple columns will now be stored in a single concatenated string field. I need to write a transact sql statement to populate the new table with historical data from the old table.
OLD TABLE:
Rec_No as int 4
type1 as smallint
type2 as smallint
type3 as smallint
typename as varchar 100
NEW TABLE:
Rec_No as int 4
Type_String as varchar 4000
Type1, type2, type3 and typename in the OLD TABLE will have to be concatenated into a single string that will be written into Type_String in the NEW TABLE.
There is one catch. Only nonzero values of type1, type2, and type3 should be added to the string (the type1, type2, and type3 columns store quantity). Its a bit more complicated than that, but I think this simplification is enough to workout the part I dont understand. I think this example in comma delimited format pretty much says it all:
OLD TABLE:
"2234", "2", "3", "0", "Gloss"
"2235", "0", "0", "1", "Plain"
"2236", "4", "4", "8", "Plain"
NEW TABLE
"2234", "Type1-Gloss-2;Type2-Gloss
"2235", "Type3-Plain-1"
"2236", "Type1-Plain-4;Type2-Plain
Thanks for your expertise and time!
-meuedyn
OLD TABLE:
Rec_No as int 4
type1 as smallint
type2 as smallint
type3 as smallint
typename as varchar 100
NEW TABLE:
Rec_No as int 4
Type_String as varchar 4000
Type1, type2, type3 and typename in the OLD TABLE will have to be concatenated into a single string that will be written into Type_String in the NEW TABLE.
There is one catch. Only nonzero values of type1, type2, and type3 should be added to the string (the type1, type2, and type3 columns store quantity). Its a bit more complicated than that, but I think this simplification is enough to workout the part I dont understand. I think this example in comma delimited format pretty much says it all:
OLD TABLE:
"2234", "2", "3", "0", "Gloss"
"2235", "0", "0", "1", "Plain"
"2236", "4", "4", "8", "Plain"
NEW TABLE
"2234", "Type1-Gloss-2;Type2-Gloss
"2235", "Type3-Plain-1"
"2236", "Type1-Plain-4;Type2-Plain
Thanks for your expertise and time!
-meuedyn
Can you try this
INSERT INTO NewTable(Rec_No, Type_String)
SELECT Rec_No, (type1+'-'+type2+'-'+type3
FROM OldTable
INSERT INTO NewTable(Rec_No, Type_String)
SELECT Rec_No, (type1+'-'+type2+'-'+type3
FROM OldTable
I think I need some sort of if...then statement to check to see if the values in type1, type2, and type3 are zero or not. This statement seems like it would just concatenate them all, no? or did I miss something? Also, the typename has to part of the string too in the format described!
>>There is one catch. Only nonzero values of type1, type2, and type3 should be added to the string
Also, nulls will kill the whole string if any exists and are added. So to replace nulls and zeros with empty string:
INSERT INTO
NewTable(Rec_No, Type_String)
SELECT
Rec_No,
(
replace(isnull(type1,''),0
) as Type_string
FROM
OldTable
Also, nulls will kill the whole string if any exists and are added. So to replace nulls and zeros with empty string:
INSERT INTO
NewTable(Rec_No, Type_String)
SELECT
Rec_No,
(
replace(isnull(type1,''),0
) as Type_string
FROM
OldTable
>>> Also, the typename has to part of the string too in the format described!
oops I missed that:
INSERT INTO
NewTable(Rec_No, Type_String)
SELECT
Rec_No,
(replace(
Case
When isnull(type1,0) <> 0 then 'Type1-' + Type1 + '-' + TypeName + '-'
Else ''
End + ';' +
Case
When isnull(type2,0) <> 0 then 'Type2-' + Type2 + '-' + TypeName + '-'
Else ''
End + ';' +
Case
When isnull(type3,0) <> 0 then 'Type3-' + Type3 + '-' + TypeName
Else ''
End
,'--','-') as Type_string
FROM
OldTable
oops I missed that:
INSERT INTO
NewTable(Rec_No, Type_String)
SELECT
Rec_No,
(replace(
Case
When isnull(type1,0) <> 0 then 'Type1-' + Type1 + '-' + TypeName + '-'
Else ''
End + ';' +
Case
When isnull(type2,0) <> 0 then 'Type2-' + Type2 + '-' + TypeName + '-'
Else ''
End + ';' +
Case
When isnull(type3,0) <> 0 then 'Type3-' + Type3 + '-' + TypeName
Else ''
End
,'--','-') as Type_string
FROM
OldTable
grr... not quite right.. sorry:
INSERT INTO
NewTable(Rec_No, Type_String)
SELECT
Rec_No,
(
Case
When isnull(type1,0) <> 0 then 'Type1-' + Type1 + '-' + TypeName + ';'
Else ''
End +
Case
When isnull(type2,0) <> 0 then 'Type2-' + Type2 + '-' + TypeName + ';'
Else ''
End +
Case
When isnull(type3,0) <> 0 then 'Type3-' + Type3 + '-' + TypeName + ';'
Else ''
End
) as Type_string
FROM
OldTable
INSERT INTO
NewTable(Rec_No, Type_String)
SELECT
Rec_No,
(
Case
When isnull(type1,0) <> 0 then 'Type1-' + Type1 + '-' + TypeName + ';'
Else ''
End +
Case
When isnull(type2,0) <> 0 then 'Type2-' + Type2 + '-' + TypeName + ';'
Else ''
End +
Case
When isnull(type3,0) <> 0 then 'Type3-' + Type3 + '-' + TypeName + ';'
Else ''
End
) as Type_string
FROM
OldTable
Not the solution you were looking for? Getting a personalized solution is easy.
Ask the ExpertsThanks for using Experts Exchange.
Create a free account to continue.
Limited access with a free account allows you to:
- View three pieces of content (articles, solutions, posts, and videos)
- Ask the experts questions (counted toward content limit)
- Customize your dashboard and profile
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.