Another technique beside selection queries in previous post, here are some of the insertion statement with example using MS SQL syntax that may help you to get understand clearer.
Insert Statement
–Subqueries help in situations when its not easy to express
–all the rows that need to be selected …… especially for
–INSERTS, UPDATES and DELETS
–In this case, I only want to see those orders that contain
–a certain product…….. in this case, ProductID=3
SELECT * FROM Orders WHERE OrderID IN ( SELECT DISTINCT OrderID FROM OrderItems WHERE ProductID=3)
INSERT Customers (FirstName, LastName, Address, City, StatePriovince, PostalCode, Country) VALUES ( ‘Bob’, ‘Tabor’, ‘1201 Wheatfield Dr.’, ‘Mesquite’, ‘TX’, ‘75088’, ‘USA’)
–UPDATES ARE THE MOST POTENTIALLY DESTRUCTIVE OF ALL
–THE SQL COMMANDS .. USE WITH EXTREME CAUTION!
–If you leave off the WHERE clause, you are in BIG trouble.
–And for this reason, NEVER perform an UPDATE or DELETE
–without first writing a SELECT statement to double
–check your WHERE clause !!!
–OR BETTER YET ….
–Make a copy of database, test your changes on the copy,
–script the SQL, make sure it works, then apply to the live database.
SELECT * FROM Customers WHERE LastName = ‘Tabor’
UPDATE Customers SET City= ‘Rowlett’, PostalCode = ‘75088’ WHERE LastName = ‘Tabor’
SLECT * FROM Customers
–SELECT * FROM Customers WHERE LastName = ‘Tabor’
DELETE Customers WHERE LastName = ‘Tabor’
–Try to delete a customer that already has an associated order
–DELETE Customers WHERE CustomerID =1
–Notice that the foreign key constraint prevents this from happening
–and creating “orphaned”rows.
SELECT * FROM Customers WHERE CustomerID =1
DELETE Customers WHERE CustomerID =1
CREATE PROCEDURE CustomerInsert @FirstName varchar(50), @LastName varchar(50), @Address varchar(50), @City varchar(50), @StateRegion varchar(50), @PostalCode varchar(50), @Country varchar(50) AS BEGIN INSERT Customers (FirstName, LastName, Address, City, StateRegion, PostalCode, Country) VALUES (@FirstName, @LastName, @Address, @City, @StateRegion, @PostalCode, @Country) END GO
