question

srinivas avatar image
srinivas asked

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.
sql-server-2008ssistsql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image
aRookieBIdev answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bopeavy avatar image
bopeavy answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.