06 | Modifying Data in SQL Server

Querying Microsoft SQL Server 2012 Jump Start

05 | SET Operators, Windows Functions, and Grouping

SET operators, Windows functions, GROUPING sets (PIVOT, UNPIVOT, CUBE, ROLLUP)

06 | Modifying Data INSERT, UPDATE, and DELETE statements, use of defaults, constraints, and triggers, OUTPUT

07 | Programming with T-SQL Using T-SQL programming elements, implementing error handling, understanding and implementing transactions

08 | Retrieving SQL Server Metadata and Improving Query Performance Querying system catalogs and dynamic management views, creating and executing stored procedures, improving SQL

Server query performance

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)VALUES

(N'Square Feet', NF2', GETDATE()), (N'Square Inches', NI2', GETDATE());

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)VALUES (N'Square Yards', N'Y2', GETDATE());GO

Using INSERT with SELECT and EXEC

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) SELECT Name, UnitMeasureCode, ModifiedDateFROM Sales.TempUnitTableWHERE ModifiedDate < '20080101';

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) EXEC Production.Temp_UOM

@numrows = 5, @catid=1;

SELECT Name, UnitMeasureCode, ModifiedDateINTO Production.TempUOMTableFROM Production.UnitMeasureWHERE orderdate < '20080101';

CREATE TABLE Production.IdentityProducts(productid int IDENTITY(100,10) NOT NULL,productname nvarchar(40) NOT NULL,categoryid int NOT NULL,unitprice money NOT NULL)

-- Define a sequenceCREATE SEQUENCE dbo.InvoiceSeq AS INT START WITH 5 INCREMENT BY 5;

-- Retrieve next available value from sequenceSELECT NEXT VALUE FOR dbo.InvoiceSeq;

UPDATE Production.UnitMeasureSET ModifiedDate = (GETDATE())WHERE UnitMeasureCode = M2

MERGE INTO schema_name.table_name AS TargetTblUSING (SELECT ) AS SourceTblON (TargetTbl.col1 = SourceTbl.col1)WHEN MATCHED THEN

UPDATE SET col2 = SourceTbl.col2WHEN NOT MATCHED THEN

INSERT ()VALUES ();

DELETE FROM Production.UnitMeasureWHERE UnitMeasureCode = Y2;

TRUNCATE TABLE Production.UnitMeasure

ALTER TABLE Production.TransactionHistoryArchiveADD CONSTRAINT PK_TransactionHistoryArchive_TransactionIDPRIMARY KEY CLUSTERED (TransactionID);

ALTER TABLE Sales.SalesOrderHeaderSalesReasonADD CONSTRAINT FK_SalesReasonFOREIGN KEY (SalesReasonID) REFERENCES Sales.SalesReason (SalesReasonID)ON DELETE CASCADE ON UPDATE CASCADE ;

CREATE TABLE Production.TransactionHistoryArchive4 (TransactionID int NOT NULL, CONSTRAINT AK_TransactionID UNIQUE(TransactionID) );

ALTER TABLE DBO.NewTableADD ZipCode int NULL CONSTRAINT CHK_ZipCodeCHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]);

ALTER TABLE Sales.CountryRegionCurrencyADD CONSTRAINT Default_CountryDEFAULT USA FOR CountryRegionCode

CREATE TRIGGER reminder1 ON Sales.CustomerAFTER INSERT, UPDATEAS RAISERROR ('Notify Customer Relations', 16, 10);

DELETE Sales.ShoppingCartItem OUTPUT DELETED.* WHERE ShoppingCartID = 20621; --Verify the rows in the table matching the WHERE clause have been deleted. SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItemWHERE ShoppingCartID = 20621;

CREATE TABLE Production.IdentityProducts(productid int IDENTITY(100,10) NOT NULL,productname nvarchar(40) NOT NULL,categoryid int NOT NULL,unitprice money NOT NULL)

-- Define a sequenceCREATE SEQUENCE dbo.InvoiceSeq AS INT START WITH 5 INCREMENT BY 5;-- Retrieve next available value from sequenceSELECT NEXT VALUE FOR dbo.InvoiceSeq;

Constraints can be used to control the behavior of SQL Server when data is

added, modified, or deleted. There are five constraints types that you can use:

PRIMARY KEY

FOREIGN KEY

UNIQUE

CHECK

DEFAULT

DML triggers are T-SQL statements used to enforce business rules and

provide data integrity when an INSERT, UPDATE, or DELETE command is

executed

CREATE TRIGGER reminder1 ON Sales.CustomerAFTER INSERT, UPDATEAS RAISERROR ('Notify Customer Relations', 16, 10);

2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.