Insert - High-Flying SQL Tips


SQL Tips

Add a Unique Constraint to a Column

CURSOR

DELETE

Dirty Reads and Phantom Reads

INSERT

Recursive SQL

ROW_NUMBER() - Automatic Paging

SELECT, FROM

SQL Default Date Format

UPDATE

WHERE

Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!

 

INSERT

Here we look at the Insert command in SQL.

The Insert command in SQL allows us to insert a row or rows into a database table, that is, adds data into the database.

In this example, we have a database table called Users, which includes columns called UserName, Password, and Address. We have 3 variables, called @UserName, @Password, @Address. These will have been set before the Insert statement. We pass these 3 variables, or rather the values which they contain, to be inserted into the Users table as a new row.


-- Insert a new user
INSERT INTO Users

(UserName, Password, Address)

VALUES

(@UserName, @Password, @Address)


So in this context, we have stated which table we would like to add data into, we specified which columns we will provide values for, and then after the Value command we provide the values which will be put into the columns we just specified.

We can also add more than one row at a time. By passing the results of a Select query, we can add multiple rows with one Insert statement.


-- Insert all the postal order ids into the PostalOrders table
INSERT INTO PostalOrders (OrderID)

SELECT ID FROM Orders WHERE OrderType = 'Postal'


So here, we are saying we are going to add row(s) to the PostalOrders database table, filling the column called OrderID with the results of the following Select statement. The Select command gathers only the column called ID, from the Orders table, but only for orders where the OrderType has been set to Postal. If there any results from the Select command, they will be in a dataset of just IDs, that is every entry (or row) will only have one column, and that row will be the ID of the order. Then the Insert command takes this dataset of however many rows it has, and adds each one into the PostalOrders table, adding each one as an individual row in the PostalOrders table.