x

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

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.
more ▼

asked Jul 11, 2011 at 04:46 PM in Default

srinivas gravatar image

srinivas
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jul 11, 2011 at 10:17 PM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 51 56 61

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jul 12, 2011 at 10:57 AM

bopeavy gravatar image

bopeavy
146 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1841
x939
x292

asked: Jul 11, 2011 at 04:46 PM

Seen: 1908 times

Last Updated: Jul 11, 2011 at 04:46 PM