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:
Target Table (BusinessKey, ClientName, Address1, Address2, City, County, STATE, Country, ClientType, Validto, ValidFrom, IsCurrent)
Thanks in Advance.
asked Jul 11, 2011 at 04:46 PM in Default
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, 2011 at 10:17 PM
Why dont you use a case statement in case you have any Nulls. example:
These values where not in the source table Validto, ValidFrom, Hope this helps