• Revista PROGRAMAR: Já está disponível a edição #53 da revista programar. Faz já o download aqui!

saunde

SQL Server 2008 instrução MERGE

1 mensagem neste tópico

A nova instrução SQL Server 2008's  MERGE permite insert, update, or delete de dados baseado em algumas condições JOIN na mesma instrução.

Em versões anteriores do SQL SERVER, era necessário criar instruções separadas, se fosse necessário fazer insert, update, ou delete numa tabela baseado em certas condições de outra tabela.

Com o MERGE, pode-se incluir a lógica para estas modificações de dados numa instrução apenas.

The following script creates the SalesArchive and SalesFeed tables:

CREATE TABLE SalesArchive

(

CustomerID INT PRIMARY KEY,

SalesDate INT,

TotalSalesAmount MONEY,

TotalSalesCount SMALLINT,

CreationDate DATETIME CONSTRAINT df_CreationDate DEFAULT(GETDATE()),

UpdatedDate DATETIME CONSTRAINT df_UpdatedDate DEFAULT(GETDATE())

)

CREATE TABLE SalesFeed

(

CustomerID INT,

Product VARCHAR(10),

SaleAmount MONEY

)

The script below loads some data into the SalesFeed table. The way in which I am inserting data into this table is new to SQL Server 2008; it allows you to specify many values to be inserted using the VALUES clause of the INSERT statement.

INSERT INTO SalesFeed

(CustomerID, Product, SaleAmount)

VALUES

(1,'PoolTable', 1000),

(2,'BigScreen', 955),

(3,'Computer', 590),

(4,'BigScreen', 880),

(5,'Computer', 700)

I have a few rows of data in my SalesFeed table and no data in my SalesArchive table. Now it is time for me to create my MERGE statement to add data to this table. Below is the MERGE script.

MERGE SalesArchive AS SA

USING (

SELECT

CustomerID,

LoadDate = MIN(CONVERT(VARCHAR( 8 ), GETDATE(), 112)),

TotalSalesAmount = SUM(SaleAmount),

TotalSalesCount = COUNT(*)

FROM SalesFeed

GROUP BY CustomerID

) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount)

ON

(

SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate

)

WHEN NOT MATCHED THEN

INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)

VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE())

WHEN MATCHED THEN

UPDATE

SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,

SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,

SA.UpdatedDate = GETDATE();

At first glance, it looks reasonably complicated, but it's not too bad once you get used to it. The table immediately following the MERGE statement is the table that will be modified; this is known as the TARGET table. In the USING statement, data from the SalesFeed table is being aggregated inside of a subquery based on the CustomerID; this portion is known as the SOURCE. This aggregation allows me to guarantee that there will be only one record per customer to update my SalesArchive table.

The ON clause of the MERGE statement is where I specify: the joining between the SOURCE, the aggregated data from the subquery, and the TARGET, the SalesArchive table.

The WHEN NOT MATCHED clause is where I specify what action I want to occur when the records from the SOURCE are not found in the TARGET. In this scenario, I want to insert those records into the SalesArchive table.

The WHEN MATCHED clause is where I specify what I need to occur when the records from the SalesArchive table and the subquery of the SalesFeed table are found. In this scenario, I want to update what is currently in the table for that day, such as the TotalSalesAmount, the TotalSalesCount, and the UpdatedDate.

With this scenario, if another sales feed comes into the database, only one statement will need to be run for that feed. Any new customer sales will be added to the database, and any existing sales will be updated with the new sales information.

Fonte : http://www.builderau.com.au/program/sqlserver/soa/Using-SQL-Server-2008-s-MERGE-statement/0,339028455,339283059,00.htm

MSDN: http://msdn2.microsoft.com/en-us/library/bb510625(SQL.100).aspx

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Crie uma conta ou ligue-se para comentar

Só membros podem comentar

Criar nova conta

Registe para ter uma conta na nossa comunidade. É fácil!


Registar nova conta

Entra

Já tem conta? Inicie sessão aqui.


Entrar Agora