Update/Insert NULL into SQL DataBase

How to Update/Insert  NULL into SQL DataBase

Update Syntax: 

Update TableName set ColumnName = NULL

Insert Syntax: 

INSERT INTO TableName (x,y,z) VALUES ( NULL,NULL,NULL)

Select Data Field using Sub Queries

Example 1:
SELECT     Epf_No,EMP_CALLING_NAME, Proc_Date,DeductAmt
FROM         TempDteSumIncentDeduct
 PS
inner join
(
Select EMP_NUMBER,EMP_CALLING_NAME
from    HS_HR_EMPLOYEE
) PS2
On PS.Epf_No = PS2.EMP_NUMBER

Note:
PS & PS2 is Temp DB


Example 2:
Select *
from
(Select EMP_NUMBER,EMP_CALLING_NAME,Epf_No,Proc_Date,DeductAmt
from    HS_HR_EMPLOYEE
PS
inner join
(
SELECT     Epf_No,Proc_Date,DeductAmt
FROM         TempDteSumIncentDeduct
where    Proc_Date = '07/11/2013'
) PS2
On PS.EMP_NUMBER = PS2.Epf_No
) as w

Note:
PS , PS2,W   is Temp DB

Crosstab queries with inner join using PIVOT in SQL Server

TABLE1 : EmpMaster 

EpfNo           Name              BathDay

100     Jone     07/01/1980  

101     Deyon    08/20/1985

102     Nimal    10/01/2000

103     Deyon    11/25/1975

TABLE1 : EmpSalary  

EpfNo        Month         PaySalary
100      Jan      25000

100      Jan        500

101      Jan      13000

101      Jan        500 

102      Jan      25000

102      Jan        500 

103      Jan      20000

100      Fab      20000

101      Fab      26800
102      Fab      13000

103      Fab      20000

SQL Query:

SELECT EpfNo, Name, BathDay, Jan, Fab
FROM 
(

   SELECT EpfNo, Name, PaySalary
   FROM EmpMaster) DB1

inner join

(

   Select  EpfNo,  Sum(PaySalary) As  Total

   From  EmpSalary 

   Group By EpfNo

)  DB2

 PIVOT
)  PS
  SUM (PaySalary)
  FOR Month IN
  ( [JAN], [FAB])
) AS pvt

After Run CrossTab Query Using PIVOT 

EpfNo        Name         BirthDay             JAN           FAB          Total        

100     Jone     07/01/1980   25500    20000   45500   

101     Deyon    08/20/1985   13500    26800   40300 

102     Nimal    10/01/2000   25500    13000   38500 

103     Deyon    11/25/1975   20500    20000   40500

On Error found in running this query

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

Crosstab queries using PIVOT in SQL Server

TABLE1 : EmpMaster 

EpfNo           Name              BathDay

100     Jone     07/01/1980  

101     Deyon    08/20/1985

102     Nimal    10/01/2000

103     Deyon    11/25/1975

TABLE1 : EmpSalary  

EpfNo        Month         PaySalary
100      Jan      25000

100      Jan        500

101      Jan      13000

101      Jan        500 

102      Jan      25000

102      Jan        500 

103      Jan      20000

100      Fab      20000

101      Fab      26800
102      Fab      13000

103      Fab      20000

SQL Query:

SELECT EpfNo, Name, BathDay, Jan, Fab
FROM 
(SELECT EpfNo, Name, PaySalary
FROM EmpMaster) DB1
PIVOT
(
SUM (PaySalary)
FOR Month IN
( [JAN], [FAB])
) AS pvt

After Run CrossTab Query Using PIVOT 

EpfNo        Name         BirthDay             JAN           FAB             

100     Jone     07/01/1980   25500    20000    

101     Deyon    08/20/1985   13500    26800   

102     Nimal    10/01/2000   25500    13000    

103     Deyon    11/25/1975   20500    20000   

How does this work?
There are three pieces  in order to construct the query.

• (1) The SELECT statement

  • SELECT EpfNo, Name, BathDay, Jan, Fab

  • This portion of the query selects the three columns for the final result set (Epfno, Name, Birthday, Jan, Fab)
  
  
• (2) The query that pulls the raw data to be prepared
  • (SELECT EpfNo, Name, PaySalary FROM EmpMaster) ps
  • This query pulls all the rows of data that we need to create the cross-tab results.  The (DB1) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
  
  
• (3) The PIVOT expression

  • PIVOT(SUM (PaySalary)FOR Month IN( [JAN], [FAB])) AS pvt

  • This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.

On Error found in running this query

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

IF...THEN in an SQL SELECT statement

IF...THEN in an SQL SELECT statement

 The simple case:

Syntax :

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
       END
FROM <table>

Example: 

SELECT  Working =
        CASE ActiveEmp
        WHEN 'N' THEN 'NO'
        ELSE 'YES'
    END

The extended case:

Syntax :

SELECT CASE WHEN <test>      THEN <returnvalue>
            WHEN <othertest> THEN <returnthis>
                             ELSE <returndefaultcase>
       END
FROM <table>

Example: 

SELECT  CASE WHEN   ActiveEmp = 'N' THEN 'NO'

             WHEN   ActiveEmp = 'Y' THEN 'YES'

                                    ELSE 'INVALID'

        END

FROM    EMPLOYEE_MASTER 

SQL Server 2012 you can use the IIF Function for this.

Syntax :

SELECT IIF(<test>, <returnthis>, <returndefaultcase>) AS <variable>, * 
FROM <table>

Example:   

SELECT IIF(Reorder = 'N' or InStock = 'Y', 1, 0) AS OrderOut, * FROM StockMaster 

In a where clause, use:

Syntax :

where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end