Tips and tricks

The command I forget, so i log it here

Using the command line

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)

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(

SELECT     ROW_NUMBER() OVER(ORDER BY DimProduct.ProductKey) AS RowNum,
 DimProduct.ProductKey, 
           DimProduct.EnglishProductName as Product,
           DimProductSubcategory.ProductSubcategoryKey as SubCategoryKey, 
           DimProductSubcategory.EnglishProductSubcategoryName as SubCategory,
           DimProductCategory.ProductCategoryKey as CategoryKey,
           DimProductCategory.EnglishProductCategoryName  as Category 
FROM         DimProduct INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
                   )   
                      SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
      AND @PageNum * @PageSize
ORDER BY ProductKey;

You can use the list down all tables in specific database with this query

select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE'

Nolock is T-Sql hint, That used to ignore the locks on table during transactions. It allows retrieving data and did not wait to complete the transaition applied on table. It has some pros can cons

Pros:

• NoLock provides significant improvements on large table, where upserts commands take time.
• You can retirve data during the same time while others are performing Insert and update on table.

Cons:

• Possibility of data that was partially updated or inserted, because you retrieve data during update or insert on table.
• It often results in very obscure, hard to reproduce bugs and can cause data to get corrupted.