Intro

The SQL command INSERT adds a row. The basic syntax is as follows:

INSERT [INTO] Table [(ColumnsList)] DataValues

The INSERT command adds row(s) into all columns of Table. The data added is specified by the DataValues. The INSERT command cannot be used for columns that are automatically generated.

The INTO keyword is optional but is a better English equivalent of what occurs and I prefer to use it.

The ColumnsList is a comma-separated list of column names that specifies which columns of Table for which there will be data added in the new row(s). The ColumnList does not have to be in the same order as they appear in the table. If the ColumnsList is not used, then it assumes that all the columns are used. Columns unnamed by the ColumnList will be entered as NULL or the default value of that column (including auto-increments and timestamps), otherwise that row cannot insert.

The DataValues must match all the columns or the ColumnList, i.e. the number of columns, and the data type, precision, and scale of each data provided must match its corresponding column. If no ColumnList is provided, then the values in DataValues must be specified in the same order as the columns in TableOrView. The DataValues comes in two forms:

  • A comma-separated list of of data values for a single row as specified by a VALUES clause:
    INSERT [INTO] Table [(ColumnList)]
    VALUES (ListOfValues)
    
    -- In MySQL and MSSQL 2008+ you can use a similar format for multiple rows:
    INSERT [INTO] Table
    [(ColumnList)]
    VALUES (ListOfValues) [, (ListOfValues2)] ...
  • Data values for one or more rows as specified by a SELECT sub-query:
    INSERT [INTO] Table [(ColumnList)]
    [SELECT statement]
    

EGs

INSERT 1 row all columns

Insert a row into all columns of a table with a list of values.

INSERT tblEmployees
VALUES (123, 'Joe', 'Smith', 'Good')

The "INTO" keyword was arbitrarily not used here.

INSERT INTO some columns

Insert 2 rows into some columns of a table with a list of values.

INSERT tblEmployees
(EmployeeID, FName, LName)
VALUES (124, 'Sue', 'Jones'), (125, 'Bob', 'Baker')

The 4th column "Evaluation" has a default value, thus input data is not required.

INSERT with sub-query

Insert rows into some columns of a table with a sub-query.

INSERT INTO tblEmployees
(EmployeeID, FName, LName)
SELECT EmployeeID, FName, LName
FROM tblTempEmployees
WHERE tblTempEmployees.Evaluation = 'Good'

INSERT non-auto incremented value

If one of the columns is auto incremented but you'd like to enter your own value, then the auto-incrementing must be turned off first. Here is a SQL Server example.

-- Next line makes an auto incrementing table.
CREATE TABLE tbl1 col1 int primary key IDENTITY(1, 1), col2 int, col3 varchar(5)
-- Next line accepts auto-increment value.
INSERT INTO tbl1 (col2, col3) VALUES (2, 'hello')
-- Next lines insert non-auto-incremented value.
SET IDENTITY_INSERT tbl1 ON
INSERT INTO tbl1 VALUES (-13, 13, 'world')
SET IDENTITY_INSERT tbl1 OFF

Only one table at a time can have IDENTITY_INSERT ON per session, so you might as well turn it off ASAP.

When you create an identity column it is not automatically set to create a unique index. IDENTITY(SeedInteger, StepInteger). See "Identity Columns" [http://www.simple-talk.com/sql/t-sql-programming/identity-columns/].

Return an IDENTITY from an INSERT

When a table is set to automatically generate the row ID, you can make SQL return that new ID. Here is the SQL combined with ADO objects using VB.

cmd = "SET NOCOUNT ON; INSERT INTO Car (Make,Model) VALUES ('Ford','Pinto'); SELECT @@IDENTITY AS NewID"
Set rst = cnn.Execute(cmd)
lastCarID = rst("NewID")

@@IDENTITY returns the last identity value inserted into that database using that connection. SET NOCOUNT ON suppresses messages like 1 Row(s) affected being sent back.

Identity issues:

  • IDENT_CURRENT('TableName'). Returns the last identity value generated for a specified table or view, in any session, and any scope.
  • @@IDENTITY. Returns the last identity value generated for any table, in the current session, and any scope.
  • SCOPE_IDENITY(). Returns the last identity value generated for any table, in the current session, and in the current scope.


GeorgeHernandez.comSome rights reserved