Solved

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

Posted on 2006-10-25
6
201 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now