Insert Update Delete

--inserts, updates and deletes
Use CommunityAssist 
Insert into Person (LastName, FirstName)
Values('Smith', 'John'),
('Doe', 'Jane')

Select * from Person

Use Master
Drop table Employee.VehicleService

Create table Person2
(
FirstName nvarchar(255),
LastName Nvarchar(255)
)

--bulk insert of sorts
Insert into Person2(FirstName, LastName)
Select FirstName, LastName From Person

Select * From Person2

--creating a temporary table
Select LastName, Firstname
into #SpecialPeople
From Person

Select * From #SpecialPeople

--update with multiple fields an 
--update should always 
--have a where clause
update Person2
Set LastName='Anders',
Firstname='bob'
Where LastName='Anderson'
And FirstName='Jay'

Select * From Person2
Drop table Person2

--explicitly using a transaction
--can give you an undo
Begin transaction

--This turns everyone's name the same
Update Person2
Set Firstname='Bob'
Where LastName='zimmerman'

Select * From Person2

Rollback tran-- will undo all transactions\
--since the begin
Commit Tran -- will commit the transaction
--to the database


Use MagazineSubscription

Select * From magazineDetail

--increase all the prices by 20%
Update MagazineDetail
Set SubscriptionPrice=SubscriptionPrice * 1.2

-- Deletions
--can't delete records that
--have child records
Use CommunityAssist
Begin Tran
Delete From Person
Where PersonKey > 51

Select * From Person
Rollback tran
Commit tran
--removes all rows because no
--related records in a child table
Delete from Person2

--does the same as delete
--but more efficient
--doesn't do it row by row
Truncate table Person2

Select * From Person2

--get table information
exec sp_help PersonAddress

--system views
Select * from sys.Databases

Select name from sys.Tables

Select name from sys.procedures

Select * From Sys.columns