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

avatar image

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

avatar image

2.8k 56 65 71

(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
     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

avatar image

157 2 3 5

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jul 11, 2011 at 04:46 PM

Seen: 3496 times

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

Copyright 2018 Redgate Software. Privacy Policy