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
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]
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.
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.