Inserts Updates and Deletes

Use MagazineSubscription

--the basic insert statement
--You must insert into every required
--column, columns and values are
--matched by sequence, first to first,
--second to second etc.
Insert into Customer(
CustLastName
, CustFirstName
, CustAddress
, CustCity
, CustState
, CustZipcode
, CustPhone)
Values(
'Custard'
, 'Colonel'
,'Library Study'
,'Seattle'
,'Wa'
,'98000'
,'2065550987')

--these are just for checking on things
Select * from customer
Select * From Magazine
Select * From MagazineDetail
Select * from SubscriptionType
Select * from Subscription

--an insert that uses a subquery and a function
Insert into Subscription (CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values((Select MAX(custID) from Customer),2,'2/7/2011', DATEADD(YY,1,'2/7/2011'))

--inserting multiple rows. This syntax only
--became available in SQL Server 2008
--otherwise had to write the complete Insert
--statement for each row
Insert Into Magazine(MagName, MagType)
Values('Trout fishers Anonymous', 'Quarterly'),
('Think Geek', 'Weekly'),
('Amiga User group', 'Annual')

--create a simple table
Create Table CallList
(
LastName nvarchar(255),
FirstName nvarchar(255),
Phone nvarchar(20)
)

--An insert that uses a select for the values
--the columns in the subquery need
--to be compatible in datatype.
--again they are matched by sequence
Insert Into CallList(LastName, FirstName, Phone)
(Select CustLastName, CustFirstName, custPhone From Customer)

Select * from CallList

--manually beginning a transaction creates
--the possibility of an undo
Begin Transaction

--update two columns in customer
--without the where clause every record
--woulc be updated
Update Customer
Set CustFirstName='Colonel',
CustAddress='Kitchen'
Where CustID=16

Select * from Customer

--if there is a mistake you can rollback
--all sql statements since the begin tran
--will be undone
Rollback Transaction

--if there is no error you can
--commit in order to write the changes
--to the database
Commit tran

--a delete statement
Delete from CallList
Where LastName='custard'
And FirstName='Colonel'

--stored procedure to get meta data
--about a table
exec sp_help Customer

--other system views
Select * from sys.Tables
Select * from sys.procedures
Select * from sys.Databases

--fully qualified columns
--running a query with values from a different
--database context
Select CommunityAssist.dbo.Person.LastName, CommunityAssist.dbo.Person.FirstName
From CommunityAssist.dbo.Person