Operador MERGE

* Novo no SQL Server 2008;

* Funciona com uma tabela SOURCE e uma TARGET, fazendo com que a TARGET fique igual a SOURCE;

* Para isso, insere na TARGET as linhas que não existem, atualiza as linhas que estão diferentes e apaga as linhas que não existem na source; Exemplo:

 

TARGET (tabela que será modificada)	SOURCE (tabela de origem)
CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY ) GO INSERT INTO Products VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 30.00), (4, 'Biscuit', 40.00) GO	CREATE TABLE UpdatedProducts ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY ) GO INSERT INTO UpdatedProducts VALUES (1, 'Tea', 10.00), (2, 'Coffee', 25.00), (3, 'Muffin', 35.00), (5, 'Pizza', 60.00) GO

 

 

FAZENDO O MERGE

--Sincronizando a tabela MERGE Products AS TARGET USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID) --Quando encontrou ID igual, faz o update WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate --Quando não encontrou ID igual, faz a inserção WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate) --Quando a linha existe na TARGET e não na SOURCe, apaga a linha WHEN NOT MATCHED BY SOURCE THEN DELETE --$action é uma coluna nvarchar(10) que indica o que foi --feito('INSERT', 'UPDATE', or 'DELETE') em cada linha OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate, INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate; SELECT @@ROWCOUNT; GO

 

Notes

• The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
• At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
• Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
• MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
• MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
• For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.

 

Artigo originial : http://www.mssqltips.com/tip.asp?tip=1704

T-SQL Settings Explained

A list of widely used SQL settings Explained

• ANSI NULLS
• QUOTED_IDENTIFIER
• ARITHMABORT
• ANSI_DEFAULTS
• ANSI_WARNINGS
• DATEFIRST
• DATEFORMAT
• NOCOUNT
• NOEXEC
• IDENTITY_INSERT
• IMPLICITY_TRANSACTIONS
• LANGUAGE

ANSI NULLS
- Syntax

SET ANSI_NULLS {ON | OFF}

- Explanation

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHEREcolumn_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.

ARITHMABORT

- Syntax

SET ARITHABORT { ON | OFF }
- Explanation

If SET ARITHABORT is ON, these error conditions cause the query or batch to terminate. If the errors occur in a transaction, the transaction is rolled back. When an INSERT, DELETE or UPDATE statement encounters an arithmetic error SQL Server inserts or updates a NULL value.

ANSI_DEFAULTS

- Syntax

SET ANSI_DEFAULTS { ON | OFF }

- Explanation

When enabled (ON), this option enables the following SQL-92 settings:
ANSI_NULLS, CURSOR_CLOSE_ON_COMMIT, ANSI_NULL_DFLT_ON, IMPLICIT_TRANSACTIONS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS.

ANSI_WARNINGS

- Syntax

SET ANSI_WARNINGS { ON | OFF }

- Explanation

When ON, if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) a warning message is generated. When OFF, no warning is issued. When ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When OFF, divide-by-zero and arithmetic overflow errors cause null values to be returned.

DATEFIRST

- Syntax

SET DATEFIRST { number | @number_var }

- Explanation

Is an integer indicating the first day of the week, and can be one of these values. 1-Monday

Use the @@DATEFIRST function to check the current setting of SET DATEFIRST.
The setting of SET DATEFIRST is set at execute or run time and not at parse time.

NOCOUNT

- Syntax

SET NOCOUNT { ON | OFF }

- Explanation

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned.

NOEXEC

- Syntax

SET NOEXEC { ON | OFF }

- Explanation

When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of Transact-SQL statements but does not execute them. Used to check if script has misspelled errors.

DATEFORMAT

- Syntax

SET DATEFORMAT { format | @format_var }

- Explanation

Is the order of the dateparts.

This example uses different date formats to handle date strings in different formats.
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = ’12/31/98′
SELECT @datevar
GO

IDENTITY_INSERT

- Syntax

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

- Explanation

When this is set ON you can insert manually values on fields with Auto Incrementation. If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO

SET IDENTITY_INSERT products ON
GO

– will succeed only if IDENTITY_INSERT IS ON
INSERT INTO products (id, product) VALUES(3, ‘garden shovel’).
GO

IMPLICITY_TRANSACTIONS

- Syntax

SET IMPLICIT_TRANSACTIONS { ON | OFF }

- Explanation

When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

alter table, fetch, revoke, create, grant, select, delete, insert, truncate table, drop, open, update.

LANGUAGE

- Syntax

SET LANGUAGE { [ N ] ‘language’ | @language_var }

[N]‘language’ |@language_var
Is the name of the language as stored in syslanguages. This argument can be either Unicode or DBCS converted to Unicode. To specify a language in Unicode, use N’language’. If specified as a variable, the variable must be sysname.

- Explanation

Specifies the language environment for the session. The session language determines the datetime formats and system messages.

Dicas

Dica de site para desbloquear PDF: http://www.ensode.net/pdf-crack.jsf