HOw to populate 1 column in target table from 2 columns in source using union all

Question

How to populate 2 columsn from source table to one column in Target table

I am implementing the SCD type 2. I have a source table where we have more columns than the target table. The reason it has to populate the same column twice. For instance in the source we have HomeAddress1 and Mail Address2 which has to populate the same column, similiarly for Homeaddress2 and Mailaddress2 and similiarly for the homecity and Mailcity etc. So we will have two records for the client Name on that particular day if we have the particular columsns for both of them.

Businesskey, ClientName, Address1,Address2,City,County,STATE,Country,ClientType, Validto, ValidFrom, IsCurrent 10050, VKR, Arbor point, parkwaydr, Irving, Dallas, TX, USA, HOME,2-21-2011 6-22-2011, Y 10050, VKR, ProvincialDR, Arlington, Dallas, Dallas, TX, USA, HOME,12-21-2010 6-22-2011, Y I know we need to perform the union all but I don’t have any clue how to go about it. I am very new to this. So Could you please tell me how to write the query for it as I am implementing SCD type 2 I don’t know how to go about it. I need to write the Merge command for the below ones:

Source Table
(SRC.ID, SRC.ClientName, HomeAddress1, MailAddress1, Homeaddress2, MailAddress2, HomeCity, MailCity, HomeCounty, MailCounty,
HomeState, MailState, HomeCountry, MailCountry, ClientType, DATE)

Target Table (BusinessKey, ClientName, Address1, Address2, City, County, STATE, Country, ClientType, Validto, ValidFrom, IsCurrent)

Thanks in Advance.

Answer 1

0

In ssis you can use a derived column which can combine the two source columns in to one column. If the two source columns are strings then you can just write something like [Column1] +[Column2] in the derived column expression.

If its other data types then you may have to type cast.

answered Jul 11 '11 at 10:17 PM

aRookieBIdev
2k ● 24 ● 40 ● 46

(comments are locked)

Answer 2

Why dont you use a case statement in case you have any Nulls. example:

insert into Target_Table 
(
    BusinessKey, ClientName, Address1, Address2, City, 
    County, STATE, Country, ClientType,  IsCurrent
)
select 
    SRC.ID, SRC.ClientName,
    case when HomeAddress1 is null then MailAddress1 else HomeAddress1 end as HomeAddress1, 
    case when HomeAddress2 is null then MailAddress2 else HomeAddress2 end as Homeaddress2, 
    case when HomeCity is null then MailCity else HomeCity end as HomeCity, 
    case when HomeCounty is null then MailCounty else HomeCounty end as HomeCounty, 
    case when HomeState is null then MailState else HomeState  end as HomeState,
    case when HomeCountry is null then MailCountry else HomeCountry end as HomeCountry,
    ClientType, DATE
from Source_Table;

These values where not in the source table Validto, ValidFrom, Hope this helps

HOw to populate 1 column in target table from 2 columns in source using union all

Follow this question

Related Questions