Chapter 12) Modifying data

USE AdventureWorks2012;
INSERT INTO HumanResources.Department(Name, GroupName, ModifiedDate)
VALUES('Payroll', 'Executive General and Administration', '6/12/2012');

USE AdventureWorks2012;
SET IDENTITY_INSERT HumanResources.Department ON
INSERT INTO HumanResources.Department(DepartmentID, Name, GroupName, ModifiedDate)
VALUES(18, 'International Marketing', 'Sales and Marketing', '5/26/2012');
SET IDENTITY_INSERT HumanResources.Department OFF

USE AdventureWorks2012
GO
IF(OBJECT_ID('dbo.States')) IS NOT NULL
	DROP TABLE dbo.States
GO
CREATE TABLE dbo.States
(
	StateID int PRIMARY KEY,
	StateName varchar(50),
	StateAbbrev char(2)
)
GO

USE AdventureWorks2012
GO
CREATE SEQUENCE dbo.StateSeq
AS int
START WITH 1
INCREMENT BY 1
GO

USE AdventureWorks2012
GO
INSERT INTO dbo.States(StateID, StateAbbrev, StateName)
VALUES	(NEXT VALUE FOR dbo.StateSeq, 'LA', 'Louisiana')
INSERT INTO dbo.States(StateID, StateAbbrev, StateName)
VALUES  (NEXT VALUE FOR dbo.StateSeq, 'TX', 'Texas')
INSERT INTO dbo.States(StateID, StateAbbrev, StateName)	
VALUES  (NEXT VALUE FOR dbo.StateSeq, 'FL', 'Florida')
GO
SELECT * FROM dbo.States

USE AdventureWorks2012;
INSERT INTO HumanResources.Department(Name, GroupName, ModifiedDate)
VALUES
('International Sales', 'Sales and Marketing', '5/26/2012'),
('Media Control', 'Quality Assurance', '5/26/2012')

USE AdventureWorks2012;
INSERT INTO HumanResources.Department(Name, GroupName, ModifiedDate)
SELECT
Name+' USA', GroupName, ModifiedDate
FROM HumanResources.Department
WHERE DepartmentID IN (20, 19)

USE AdventureWorks2012;
SELECT
DepartmentID, Name, GroupName, ModifiedDate
INTO dbo.Department
FROM HumanResources.Department

USE AdventureWorks2012;
UPDATE HumanResources.Department
SET Name = Name +' Europe'
WHERE DepartmentID = 19

UPDATE Production.Product
SET ListPrice = p.ListPrice * 1.05
FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE
ps.Name = 'Socks'

USE AdventureWorks2012;
DELETE FROM HumanResources.Department
FROM HumanResources.Department d
LEFT OUTER JOIN HumanResources.EmployeeDepartmentHistory ed
ON d.DepartmentID = ed.DepartmentID
WHERE ed.DepartmentID IS NULL

USE AdventureWorks2012;
TRUNCATE TABLE dbo.Department

USE AdventureWorks2012;
MERGE dbo.Department destination
USING HumanResources.Department source
ON destination.Name = source.Name
WHEN MATCHED THEN
UPDATE
SET destination.Name = source.Name,
destination.GroupName = source.GroupName,
destination.ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, GroupName, ModifiedDate)
VALUES (source.Name, source.GroupName, source.ModifiedDate);

USE AdventureWorks2012;
INSERT INTO HumanResources.Department
OUTPUT inserted.DepartmentID, inserted.Name, inserted.GroupName, inserted.
ModifiedDate
VALUES('International Marketing', 'Sales and Marketing', '5/26/2012');

USE AdventureWorks2012;
UPDATE HumanResources.Department
SET Name = Name +' Europe'
OUTPUT
deleted.Name AS OldName,
inserted.Name AS UpdateValue
WHERE DepartmentID = 23 AND
Name NOT LIKE '% Europe'

USE AdventureWorks2012;
GO
CREATE TABLE dbo.Department_Audit
(
DepartmentID int NOT NULL,
Name nvarchar(50) NOT NULL,
GroupName nvarchar(50) NOT NULL,
DeletedDate datetime NOT NULL
CONSTRAINT DF_Department_Audit_DeletedDate_Today DEFAULT(GETDATE())
)

USE AdventureWorks2012;
DELETE FROM dbo.Department
OUTPUT deleted.Departmentid, deleted.Name, deleted.GroupName
INTO dbo.Department_Audit(DepartmentID, Name, GroupName)
WHERE DepartmentID = 16

Chapter 13) Built-in scalar functions

SELECT GETDATE() AS GETDATE, SYSDATETIME() AS SYSDATETIME;

SELECT
DAY(GETDATE()) AS DAY,
MONTH(GETDATE()) AS MONTH,
YEAR(GETDATE()) AS YEAR,
DATENAME(WEEKDAY, GETDATE()) AS DATENAMEWeekDay,
DATEPART(M, GETDATE()) AS DATEPART,
DATEPART(WEEKDAY, GETDATE()) AS DatePartWeekDay,
DATENAME(MONTH, GETDATE()) AS DateNameMonth

SELECT
DATEFROMPARTS ( 1972, 5, 26) AS DATEFROMPARTS,
DATETIME2FROMPARTS ( 1972, 5, 26, 7, 14, 16, 10, 3 ) AS DATETIME2FROMPARTS,
DATETIMEFROMPARTS ( 1972, 5, 26, 7, 14, 16, 10) AS DATETIMEFROMPARTS,
DATETIMEOFFSETFROMPARTS ( 1972, 5, 26, 7, 14, 16, 10, 12, 0, 3 ) AS DATETIMEOFFSETFROMPARTS,
SMALLDATETIMEFROMPARTS ( 1972, 5, 26, 7, 14) SMALLDATETIMEFROMPARTS,
TIMEFROMPARTS(7, 14, 16, 10, 3) TIMEFROMPARTS

SELECT
DATEDIFF(dd, GETDATE(), '5/26/2013') AS DaysUntilMyBirthday,
DATEADD(y, 1, GETDATE()) AS DateAdd,
EOMONTH(GETDATE()) AS EOMonth, --New to SQL Server 2012
ISDATE(GETDATE()) AS IsValidDate,
ISDATE('13/1/2122') AS InvalidDate,
DATEPART(y, GETDATE()) AS DayOfYear

USE AdventureWorks2012;
SELECT TOP(10)
SalesOrderNumber,
TotalDue,
CAST(TotalDue AS decimal(10,2)) AS TotalDueCast,
OrderDate,
CAST(OrderDate AS DATE) AS OrderDateCast
FROM Sales.SalesOrderHeader;

SELECT
CONVERT(VARCHAR(20), GETDATE()) AS [Default],
CONVERT(VARCHAR(20), GETDATE(), 100) AS DefaultWithStyle,
CONVERT(VARCHAR(10), GETDATE(), 103) AS BritishFrenchStyle,
CONVERT(VARCHAR(8), GETDATE(), 105) AS ItalianStyle,
CONVERT(VARCHAR(8), GETDATE(), 112) AS ISOStyle,
CONVERT(VARCHAR(15), CAST('111111.22' AS MONEY), 1) AS MoneyWithCommas