In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
COURSE of the MONTH
15 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how do I populate one table from another and concatenate fields in the process?
Posted on 2006-10-25
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
6 Comments
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
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month15 days, 17 hours left to enroll
743 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.