INSERT

Contact Us or call 1-877-932-8228
INSERT

INSERT

To insert a record into a table, you must specify values for all fields that do not have default values and cannot be NULL.

Syntax

INSERT INTO table
(columns)
VALUES (values);

The second line of the above statement can be excluded if all required columns are inserted and the values are listed in the same order as the columns in the table. We recommend you include the second line all the time though as the code will be easier to read and update and less likely to break as the database is modified.

Code Sample:

InsertsUpdatesDeletes/Demos/Insert.sql
-- Insert a New Employee

/******************************
Both of the inserts below will work in SQL Server

Oracle
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy,
	BirthDate, HireDate, Address, City, Region,
	PostalCode, Country, HomePhone, Extension)
VALUES ('Dunn','Nat','Sales Representative','Mr.','19-Feb-1970',
	'15-Jan-2004','4933 Jamesville Rd.','Jamesville','NY',
	'13078','USA','315-555-5555','130');

/******************************
MySQL
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy,
	BirthDate, HireDate, Address, City, Region,
	PostalCode, Country, HomePhone, Extension)
VALUES ('Dunn','Nat','Sales Representative','Mr.','1970-02-19',
	'2004-01-15','4933 Jamesville Rd.','Jamesville','NY',
	'13078','USA','315-555-5555','130');

If the INSERT is successful, the output will read something to this effect:

(1 row(s) affected)

In the presentation that follows, the instructor states that the record ID is auto-generated by SQL when a record is inserted. While this is true for SQL Server and MySQL, it is not true for Oracle. In Oracle, an INSERT requires that the user specify a record ID. Therefore, this is an additional difference in addition to the date format between the three databases.

Next