Merge Concept with Output Operation.

CREATE TABLE ReadOnlyTable (

    Id INT PRIMARY KEY,

    Col1 INT,

    Col2 INT,

    Timestamp DATETIME

)

 

CREATE TABLE HistoryTable (

    Id INT PRIMARY KEY,

    Col1 INT,

    Col2 INT,

    Timestamp DATETIME

)

GO

 

INSERT INTO ReadOnlyTable (Id, Col1, Col2, Timestamp)

VALUES

    (1, 1, 1, GETDATE()),

    (2, 2, 2, GETDATE()),

    (3, 3, 3, GETDATE())

GO

 

Merge Concept:

MERGE INTO HistoryTable Dst

USING ReadOnlyTable Src ON Dst.id = Src.id

WHEN MATCHED AND (Dst.col1 <> Src.col1 OR Dst.col2 <> Src.col2 OR Dst.Timestamp <> Src.Timestamp) THEN

    UPDATE

    SET Dst.col1 = Src.col1, Dst.col2 = Src.col2, Dst.Timestamp = src.Timestamp

WHEN NOT MATCHED BY TARGET THEN

    INSERT (id, col1, col2, Timestamp)

    VALUES (Src.id, Src.col1, Src.col2, Src.Timestamp)

WHEN NOT MATCHED BY SOURCE THEN

    DELETE

OUTPUT

    GETDATE() AS ChangeDate,

    COALESCE(Inserted.Id, Deleted.Id) AS Id,

    CASE

        WHEN Deleted.Id IS NULL AND Inserted.Id IS NOT NULL THEN 'i'

        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NOT NULL THEN 'u'

        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NULL THEN 'd'

    END AS ChangeType,

    deleted.col1 AS col1_before,

    deleted.col2 AS col2_before,

    deleted.Timestamp AS Timestamp_before,

    inserted.col1 AS col1_after,

    inserted.col2 AS col2_after,

    inserted.Timestamp AS Timestamp_after;

 

Example For Insert,Update,Delete It will track with Output Process

 

UPDATE ReadOnlyTable SET Col1 = 42

WHERE Id = 1

GO

DELETE FROM ReadOnlyTable

WHERE Id = 2

GO

INSERT INTO ReadOnlyTable (Id, Col1, Col2, Timestamp)

VALUES (4, 4, 4, GETDATE())

GO

 

Now  We Track the Output  So Create a new table

CREATE TABLE ReadOnlyTableChanges

(

    ChangeId INT IDENTITY PRIMARY KEY,

    ChangeDate DATETIME,

    Id INT,

    ChangeType CHAR(1),

    Col1_before INT,

    Col2_before INT,

    Timestamp_before DATETIME,

    Col1_after INT,

    Col2_after INT,

    Timestamp_after DATETIME

)

GO

 

 

Merge Concept: Track Information Insert into Tracking Table

MERGE INTO HistoryTable Dst

USING ReadOnlyTable Src ON Dst.id = Src.id

WHEN MATCHED AND (Dst.col1 <> Src.col1 OR Dst.col2 <> Src.col2 OR Dst.Timestamp <> Src.Timestamp) THEN

    UPDATE

    SET Dst.col1 = Src.col1, Dst.col2 = Src.col2, Dst.Timestamp = src.Timestamp

WHEN NOT MATCHED BY TARGET THEN

    INSERT (id, col1, col2, Timestamp)

    VALUES (Src.id, Src.col1, Src.col2, Src.Timestamp)

WHEN NOT MATCHED BY SOURCE THEN

    DELETE

OUTPUT

    GETDATE() AS ChangeDate,

    COALESCE(Inserted.Id, Deleted.Id) AS Id,

    CASE

        WHEN Deleted.Id IS NULL AND Inserted.Id IS NOT NULL THEN 'i'

        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NOT NULL THEN 'u'

        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NULL THEN 'd'

    END AS ChangeType,

    deleted.col1 AS col1_before,

    deleted.col2 AS col2_before,

    deleted.Timestamp AS Timestamp_before,

    inserted.col1 AS col1_after,

    inserted.col2 AS col2_after,

    inserted.Timestamp AS Timestamp_after

 INTO ReadOnlyTableChanges; --This is added

Common Table Expression(CTE) With Concept in Sql server.

CTE with multiple references

;with
cte_Orders
AS
(
    SELECT
        ID
        ,Description
        ,OrderDate
    FROM
        Orders
    WHERE
        OrderDate BETWEEN '2011-03-01' AND '2011-03-31'
)
,cte_OrderParts
AS
(
    SELECT
        SUM(UnitPrice * Quantity) AS Amount
        ,Order_ID
    FROM
        OrderParts
            INNER JOIN cte_Orders ON Order_ID = cte_Orders.ID
    GROUP BY
        Order_ID
)
,cte_OrderLabor
AS
(
    SELECT
         SUM(UnitPrice * Quantity) AS Amount
        ,Order_ID
    FROM
        OrderLabor
            INNER JOIN cte_Orders ON Order_ID = cte_Orders.ID
    GROUP BY
        Order_ID
)
SELECT
    ID
    ,Description
    ,OrderDate
    ,COALESCE(cte_OrderParts.Amount, 0.00) AS PartsAmount
    ,COALESCE(cte_OrderLabor.Amount, 0.00) AS LaborAmount
FROM
    cte_Orders
        LEFT JOIN cte_OrderParts ON ID = cte_OrderParts.Order_ID
        LEFT JOIN cte_OrderLabor ON ID = cte_OrderLabor.Order_ID

with ct (CityID,StateID,CityName) as
(
Select CityID,StateID,CityName
from City
)
,
st (StateID,StateName, regionID) as
(
Select StateID,StateName, regionid from state
)
,
rt (RegionID,RegionName) as
(
Select rr.RegionID,rr.RegionName from region rr
join st on st.RegionID = rr.RegionID
)
Select ct.CityName, st.StateName, rt.RegionName
From CT
join ST on ct.stateID = st.stateID
join rt on rt.RegionID = rt.RegionID

in above query the ST cte is being used twice.

_________________________________________________________________\

WITH

  cteTotalSales (SalesPersonID, NetSales)

  AS

  (

    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)

    FROM Sales.SalesOrderHeader

    WHERE SalesPersonID IS NOT NULL

    GROUP BY SalesPersonID

  )

SELECT

  sp.FirstName + ' ' + sp.LastName AS FullName,

  sp.City + ', ' + StateProvinceName AS Location,

  ts.NetSales

FROM Sales.vSalesPerson AS sp

  INNER JOIN cteTotalSales AS ts

    ON sp.BusinessEntityID = ts.SalesPersonID

ORDER BY ts.NetSales DESC

 

DB Backup Query.

Conventional and Split File Backup and Restore

Just a day before working on one of the projects, I had to take a backup of one database of 14 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 8 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 7 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Let us see an example of a conventional one-file backup using the AdventureWorks database.

BACKUP DATABASE DATABASENAME

TO DISK='C:\BACKUP\SINGLE\DATABASENAME.BAK'

 

let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE DATABASENAME

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME1.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME2.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME3.BAK'

 

 

Restoring a backup from a single-file backup is quite easy. Let us go over an example where we restore the AdventureWorks database from a single backup file

RESTORE DATABASE DATABASENAME

TO DISK='C:\BACKUP\SINGLE\DATABASENAME.BAK'

 

let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

 

RESTORE DATABASE DATABASENAME

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME1.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME2.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME3.BAK'

 

Mirror Backup of the file

It is quite a common practice to create an exact copy of the backup and store it to several places to deal with any catastrophes which might affect the place where the database is stored. Once a full backup is accomplished DBAs generally copy the database to another location in their network using a third party tools like robocopy or native DOS commands like xcopy.

In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.

Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.

Example 1. Single File Backup to Multiple Locations using Mirror

BACKUP  DATABASE  DATABASENAME

TO= 'C:\BACKUP\SINGLE\DATABASENAME.BAK'

MIRROR TO DISK='C:\BACKUP\MIRROR\DATABASENAME.BAK'

WITH FORMAT

If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMAT reinitializes the backup.

 

Split File Backup to Multiple Locations using Mirror

We have earlier seen an example where we can have multiple split files of large database backup files. SQL Server Mirror functionality also supports backup of the split files

 

BACKUP DATABASE DATABASENAME

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME1.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME2.BAK'

TO DISK='C:\BACKUP\MULTIPLE\DATABASENAME3.BAK'

MIRROR TO DISK='C:\BACKUP\MIRROR\DATABASENAME1.BAK'

                      DISK='C:\BACKUP\MIRROR\DATABASENAME2.BAK'

                      DISK='C:\BACKUP\MIRROR\DATABASENAME3.BAK'

WITH FORMAT

 

 

REFRESH THE VIEW

EXEC sp_refreshview 'LimitView4'

EXEC SP_REFRESHVIEW  'VIEWNAME'

               

 

 

 

 

 

Windows Authentication Admin User Creation

EXEC

master..sp_addsrvrolemember @loginame = N'Medallcorp\basheer', @rolename = N'sysadmin'

GO

How To Get Week Start date and Week End date

--GET Start Day of Week

 

declare @no int,@date datetime=GETDATE()

select @no=DATEPART(WEEKDAY,@date)

select @date-(@no-1)

--GET End Day of Week

 

declare @no int,@date datetime=GETDATE()

select @no=DATEPART(WEEKDAY,@date)

select @date+(7-@no)

Database Mail configuration and Maximum Allowable file size

1)          In the Object Explorer frame of SQL Server Management Studio, expand the "Management" folder. Open the Database Mail configuration wizard.

2)         Click Next-> Choose the radio button for "View of change system parameters" and click Next.

3)           Now change the Maximum File Size (Bytes) property and click Next, then click Finish to save your changes

                Maximum file Size: '1000000000'

insert into tablename(column1,2,3...) exec spname ,using parameter table

Declare

@tablevar table(centername varchar(100),collection bigint,[transaction date] datetime,GrossAmount bigint,Discount bigint,NetAmount bigint,Due bigint,card bigint,cheque bigint,totalcollection bigint,center int,cash bigint)

insert into @tablevar(centername,collection,[transaction date],GrossAmount,Discount,NetAmount,Due,cash,card,cheque,totalcollection,center) Exec [usp_DayBookTotal] '12/10/2011','12/10/2011','%',1,'100'

SELECT centername,collection,[transaction date],GrossAmount,Discount,NetAmount,Due,card,cheque,totalcollection,center FROM @tablevar