Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

SQL Server 2008

Using a Common Table Expression (CTE) with an UPDATE statement

by

For update part of ETL Process I have to write another Common table expression to get  all records where are changed at source to run update query on destination table

 

My update part of basic ETL using TSQL as

 

with ChangedRows 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 inner join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

where

 

sourceEmp.lastname <> DestEmployee.lastname

or sourceEmp.firstname <> DestEmployee.firstname

or sourceEmp.title <> DestEmployee.title

or sourceEmp.titleofcourtesy <>DestEmployee.titleofcourtesy

or sourceEmp.birthdate<>DestEmployee.birthdate

or sourceEmp.hiredate <> DestEmployee.hiredate

or sourceEmp.address <> DestEmployee.address

or sourceEmp.city <> DestEmployee.city

or sourceEmp.region <> DestEmployee.region

or sourceEmp.postalcode<>DestEmployee.postalcode

or sourceEmp.country <>DestEmployee.country

or sourceEmp.phone<>DestEmployee.phone

or sourceEmp.mgrid <> DestEmployee.mgrid

 

)

update  TSQLFundamentals2008DW.HR.DimEmployees

set    lastname = ChangedRows.lastname

,  firstname = ChangedRows.firstname

,  title = ChangedRows.title

,  titleofcourtesy =ChangedRows.titleofcourtesy

,  birthdate=ChangedRows.birthdate

,  hiredate = ChangedRows.hiredate

,  address = ChangedRows.address

,  city = ChangedRows.city

,  region = ChangedRows.region

,  postalcode=ChangedRows.postalcode

,  country =ChangedRows.country

,  phone=ChangedRows.phone

,  mgrid = ChangedRows.mgrid

from ChangedRows

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId=ChangedRows.empid

BIDS Helper

by

http://bidshelper.codeplex.com/

There are many advantages in using this tool…
1. The first thing which I use this is for checking the dimension health, this is very helpful especially when you are dealing with the hierarchies…this helps in checking the data which is violating the hierarchy rule like data with many to many relationship etc…
2. There are many other useful options available like smartdiff (for finding the difference in code between versions), Deleting the Report Dataset Catch files (to make sure we see the refreshed data), Roles Report (gives the detail on the roles used in the cube) etc…
I would strongly recommend using this tool as this helps in improving and analysing many things while doing the BI development work.

A Visual Studio.Net add-in with features that extend and enhance the functionality of the SQL Server 2008 BI Development Studio (BIDS).

 

Features

 

How to get Tables list in database TSQL

by

 

Main object is sys.tables. you can get all information about tables by query on sys.tables.

 

User databasename

Select * from sys.tables;.

 

 

SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_Name

FROM  sys.tables;

 

 

SCHEMA_NAME convert  schema_id into schema name.