SQL INSERT

Introduction

Syntax

INSERT Syntax Version 1

The INSERT statement inserts a single new tuple into a database table. The column names and expressions must be in matching order. 

          
INSERT INTO [owner.]table-name [(column-name, ...)]
... VALUES (expression, ...)

INSERT Syntax Version 2

Inserts new tuples into a target database table by selecting from a source table.

          
INSERT INTO [owner.]target-table [(column-name, ...)]
SELECT [(column-name, ...)]
FROM source-table
WHERE condition;

Examples

member INSERT
              
INSERT INTO member
( memberSurname, memberForename, memberAddress, memberEmail )
VALUES
( 'Brown', 'David', 'No Fixed address', 'dbrown@wlu.ca' )

Inserts the given values into single tuple in member. memberId is not given since it has a default (AUTOINCREMENT) value.

pub INSERT
              
INSERT INTO pub
(pubMemberId, pubTitle, pubJournal, pubPublisher)
VALUES
(5, 'Murphy''s World', NULL, 'Peregrine')
  • Inserts the given values into pub. pubId is given a default AUTOINCREMENT value.
  • NULL values may be entered explicitly, if allowed in that column. Had pubJournal been left out of the column list, it would have received a NULL value by default, since no other default value had been specified for that column.
  • Single quotes as data rather than delimiters (Murphy's) must be entered in pairs, although they are stored and displayed singly.