This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Course Of The Month
3 days, 21 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
Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses
Course of the Month3 days, 21 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.