Solved

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

Posted on 2006-10-25
6
202 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

19 Experts available now in Live!

Get 1:1 Help Now