The SQL INSERT Statement

A short video covering the INSERT statement in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 3/15/2016
-- INSERT - how to create new data

USE Northwind

-- might want to review the SELECT statement
-- before using INSERT

-- public service announcement
/* ***WARNING!*** *****************

*INSERT is a *data modifying* command

*INSERT can do set-at-a-time operations

**this means that a seemingly simple INSERT statement
** can change millions of records

* the database engine will not ask "Are you sure?"
* there is no simple "Undo"

* professionals normally don't, at the command line,
* execute INSERT, UPDATE, and DELETE commands
* on a production database

* treat these statements with respect!

*************************************/

-- the INSERT statement creates entire rows
-- in a table that already exists

-- the form of a table must be maintained

-- in other words, a table is always a rectangle

-- every row has the same number of columns
-- every column has the same number of rows

-- you can't INSERT half a row
-- you can't INSERT a new value in 
--  a row that already exists (you'd use UPDATE)

-- let's look at the Orders table

SELECT *
FROM   Orders
ORDER BY OrderID desc

-- Here is a basic statement to create 
-- a new record in the Orders table

INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('Akron', 'VINET')

-- note that only two fields are specified
-- and CustomerID comes before ShipCity in the table definition


-- OK, let's see if it worked
SELECT   *
FROM     Orders
ORDER BY OrderID desc

-- notice an entire row was created
-- even though we supplied only two field values

-- most of the unspecified fields got NULL values

-- however, OrderID and Freight got non-NULL values
-- to see why, let's look at the  table defition

/*
CREATE TABLE Orders
 OrderID        int IDENTITY(1,1) NOT NULL,               <-----******
 CustomerID     nchar(5)          NULL,
 EmployeeID     int               NULL,
 OrderDate      datetime          NULL,
 RequiredDate   datetime          NULL,
 ShippedDate    datetime          NULL,
 ShipVia        int               NULL,
 Freight        money             NULL CONSTRAINT [DF_Orders_Freight]  DEFAULT ((0)),      <-----*****
 ShipName       nvarchar(40)      NULL,
 ShipAddress    nvarchar(60)      NULL,
 ShipCity       nvarchar(15)      NULL,
 ShipRegion     nvarchar(15)      NULL,
 ShipPostalCode nvarchar(10)      NULL,
 ShipCountry    nvarchar(15)      NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
 OrderID ASC
)
*/

-- the two key pieces above are:
-- the IDENTITY(1,1) for OrderID and the DEFAULT((0)) for Freight

-- IDENTITY(1,1) for OrderID means that the database engine
--    will provide a unique value for this field
--    beginning with 1, and incrementing by 1 for each new record
--    we *cannot* provide a value for this field
--    it is completely controlled by the database engine
--
--    this is commonly used for primary key fields

-- the DEFAULT((0)) for Freight means that if no value
--    is provided for Freight, the database engine will set the 
--    value to zero
--    

-- so back to the INSERT statement

-- the general form of the INSERT statement is:
/*
INSERT INTO  ()
VALUES                  ()
*/

-- more specifically:
/*
INSERT INTO  (col1,       col2,       col3,...)
VALUES                  (col1-value, col2-value, col3-value,...)
*/

-- note that col1, col2, etc, can be specified in any order
-- they don't have to be consistent with the table definition

-- so here's an insert into the Products table

INSERT INTO Products 
           (ProductName, 
            SupplierID, 
            CategoryID, 
            QuantityPerUnit, 
            Unitprice, 
            UnitsInStock,
            UnitsOnOrder,
            ReorderLevel,
            Discontinued)
VALUES     ('Bowzer Biscuits', 
            2,
            3,
            NULL,
            12.0,
            10,
            5,
            8,
            0)

-- I've formatted this differently, for readability

-- let's see if it worked
SELECT   *
FROM     Products
ORDER BY ProductID desc

-- notice that the productID got a value, even though I didn't provide it

-- also notice that the bit field discontinued was specified as 0

-- and let's go back to orders, and specify some dates
INSERT INTO Orders
           (customerID,
            EmployeeID,
            OrderDate,
            RequiredDate,
            ShippedDate,
            Freight)
VALUES     ('VINET',
            3,
            GETDATE(),
            '20160501 17:00:00.000', -- 5:00pm May 1 2016
            NULL,
            NULL)


SELECT      *
FROM        Orders
ORDER BY    OrderID desc

-- notice that character-based data is delimited by single quotes
--    that I can use GETDATE() to provide the current server datetime value for the orderdate
--    and that dates are specified as delimited characters, and converted to datetime

-- so what happens if I provide an invalid value?

INSERT INTO Orders
            (orderID)
VALUES      (1)
-- Cannot insert explicit value for identity column in table 'Orders' 
--    when IDENTITY_INSERT is set to OFF.
-- IDENTITY fields can only be set by the database engine

INSERT INTO Orders
            (customerID)
VALUES      ('Barney')
-- String or binary data would be truncated.
-- CustomerID is a nchar(5), and 'Barney' has 6 letters

INSERT INTO Orders
            (customerID)
VALUES      ('Barne')

--The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". 
-- The conflict occurred in database "Northwind", table "dbo.Customers", column 'CustomerID'.
-- 'Barne' is not a valid customerID - it doesn't exist in the Customers table


-- in summary,
-- INSERT creates entire rows
-- specify values according to their data type - you have to know the data types
-- various things could "block" an insert
-- these "blocks" are your friends! they keep your data clean!
-- treat INSERT, UPDATE, and DELETE with respect!


-- Database by Doug
-- Douglas Kline
-- 3/15/2016
-- INSERT - how to create new data