SQL Query :- Create Tables,Primary key,Foreign key,Merge Statment

SQL Query

1)    Using CASE Statement : - To Check the Particular column ISNULL

             CASE WHEN ISNULL ([Col_Name], ' ') = ' ' THEN 'Others'        
              ELSE [Col_Name] END AS [Col_Name]

2)    Convert Data format into VARCHAR and save 10/18/2012 this Format and also check if Date Column is Empty or not.
                CONVERT (VARCHAR,[Col_CreatedDt], 101) AS CreatedDt         
              , CASE WHEN ISNULL ([Col_ModifiedDt], ' ') = ' ' THEN ' not available'       
                ELSE CONVERT (VARCHAR, [ Col_ModifiedDt], 101) END AS ModifiedDt

3)    Casting  and Check Date Difference
              CAST (Col_Name or ParameterName AS VARCHAR)
              DATEDIFF(HOUR,Col_Date, GETDATE())<=24
4)    Create two table and set Primary key and Foreign Key

            Create Table Exams
           (
                exam_id int primary key,
                exam_name varchar(50),
           );
           Create Table question_bank
          (
             q_id uniqueidentifier primary key,
             q_exam_id uniqueidentifier not null,
             Foreign Key (q_exam_id) References exams (exam_id)
         );
 

5)    Set Primary key and  Foreign key for Existing Table using ALTER Query
              CREATE TABLE [dbo].[Users]
             (
               [ID] [bigint] IDENTITY (1, 1) NOT NULL,
               [Email] [nvarchar](89) NOT NULL
             )

             ALTER TABLE [Users]
            ADD CONSTRAINT PK_UsersID PRIMARY KEY (ID);

            CREATE TABLE [dbo].[Investments]
           (
            [ID] [bigint] NOT NULL,
            [UserID] [bigint] NOT NULL
           )

          ALTER TABLE Investments
          ADD CONSTRAINT PK_InstestmentsID PRIMARY KEY (ID);

         ALTER TABLE Investments
         ADD CONSTRAINT FK_UsersInvestments
         FOREIGN KEY (UserID)
         REFERENCES Users (ID);

6)    Creating Merge Statement in Store Procedure to

       Before creating Merge statement we should create User-define Table with columns which is the column  you want to insert.

     Eg:-
       CREATE TYPE [dbo].[UserTypeName] AS TABLE
      (   
        [ID] [int] NOT NULL,
        [DecimalCol] Decimal (16, 2) NULL
     )

   
)
CREATE PROCEDURE SP_Name     
(   
     @ParameterDt UserTypeName READONLY
) 

BEGIN TRY   
             
   MERGE INTO [dbo].[TableName] AS TARGET               
   USING @ ParameterDt AS SOURCE                
   ON TARGET.[ID] = SOURCE.[ID]  
  WHEN MATCHED AND SOURCE.[ DecimalCol] IS NULL THEN 
  DELETE  
  WHEN MATCHED AND SOURCE.[ DecimalCol] IS NOT NULL THEN             
  UPDATE SET TARGET.[ID]  = SOURCE.[ID]               
   , TARGET.[ DecimalCol]  = SOURCE.[ DecimalCol]      
  WHEN NOT MATCHED AND SOURCE.[ DecimalCol] IS NOT NULL        
  THEN               
  INSERT (ID], [DecimalCol])               
   VALUES (SOURCE.[ID],SOURCE.[DecimalCol]);   
    
 END TRY