SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 89 | White Clover Markets | Karl Jablonski | 305 — 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
| 90 |
INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:
Example
The selection from the "Customers" table will now look like this:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 89 | White Clover Markets | Karl Jablonski | 305 — 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
| 90 |
Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):
Example
The selection from the "Customers" table will now look like this:
Add row to query result using select
Is it possible to extend query results with literals like this?
so it returns all the names in the table plus ‘JASON’, or (25,’Betty’).
3 Answers 3
You use it like this:
Use UNION ALL to allow duplicates: if there is a 25-years old Betty among your users, the second query will not select her again with mere UNION .
In SQL Server, you would say:
In Oracle, you would say
is it possible to extend query results with literals like this?
- Use UNION to add Jason if it isn’t already in the result set.
- Use UNION ALL to add Jason whether or not he’s already in the result set.
-
The Overflow Blog
Linked
Related
Hot Network Questions
Subscribe to RSS
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.5.23.43453
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.