My current assignment was to write ETL Process using TSQL. So I have to get all rows which are newly added to Source Table and are not part of destination table. I wrote simple Common Table Expression to get all rows and insert into destination table.
So part of ETL Process using CTE as
with NewRows as ( SELECT sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId FROM TSQLFundamentals2008.HR.Employees sourceEmp left join TSQLFundamentals2008DW.HR.DimEmployees DestEmployee on sourceEmp.empid = DestEmployee.SourceEmpId where DestEmployee.SourceEmpId is null ) INSERT INTO [TSQLFundamentals2008DW].[HR].[DimEmployees] ([lastname] ,[firstname] ,[title] ,[titleofcourtesy] ,[birthdate] ,[hiredate] ,[address] ,[city] ,[region] ,[postalcode] ,[country] ,[phone] ,[mgrid] ,[SourceEmpId]) (select NewRows.lastname, NewRows.firstname, NewRows.title, NewRows.titleofcourtesy, NewRows.birthdate, NewRows.hiredate, NewRows.address, NewRows.city, NewRows.region, NewRows.postalcode, NewRows.country, NewRows.phone, NewRows.mgrid, NewRows.empid from NewRows)