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
Solved

how do I populate one table from another and concatenate fields in the process?

Posted on 2006-10-25
6
204 Views
Last Modified: 2012-06-21
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-3"
"2235", "Type3-Plain-1"
"2236", "Type1-Plain-4;Type2-Plain-4;Type3-Plain-8"

Thanks for your expertise and time!

-meuedyn







0
Comment
Question by:meuedyn
  • 3
  • 2
6 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 17806008
Can you try this

INSERT INTO NewTable(Rec_No, Type_String)
SELECT Rec_No, (type1+'-'+type2+'-'+type3) as Type_string
FROM OldTable
0
 

Author Comment

by:meuedyn
ID: 17806079
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!
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 17806092
>>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,'') + ';' + replace(isnull(type2,''),0,'') + ';' + replace(isnull(type3,''),0,'')
     ) as Type_string
FROM
     OldTable
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 15

Expert Comment

by:MNelson831
ID: 17806143
>>>  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
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 500 total points
ID: 17806174
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
0
 

Author Comment

by:meuedyn
ID: 17882539
thanks MNelson, did my import and it worked great.:)  I took out the isnull, btw, couldnt make it work, but its fine,.. I verified it in another way.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS - Date Report Options 2 27
TSQL Challenge... 7 35
Row insertion failed. Array 5 46
Need help in debugging a UDF results 7 19
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question