Solved

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

Posted on 2006-10-25
6
203 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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