Sometimes you need to write a record into a table in a SQL database. The INSERT INTO statement creates new records in a table and can work in one of two ways. The first form does not specify the column names where the data will be inserted, only their values. When doing so, each value needs to be inserted in the columned order they appear, here the table being the name of the table you’re adding a record into and each value would be replaced with the contents of your value (don’t insert the string ‘value’ into each!):
INSERT INTO table
VALUES (value,value,value,...);
If you don’t have every value to insert, you can also list the columns to insert data in and then include the values in the same order that the columns are listed in. The second form specifies both the column names and the values to insert:
INSERT INTO table (column,column,column,...)
VALUES (value,value,value,...);
Below is a selection from the “Customers” table that I put in the first article. We will use it to run some SQL statements using the INSERT keyword so that we can add data to our little database:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
To insert a new row in the “Customers” table, we’ll add a row for Huffington Post with Arianna Huffington as the owner, her address as “The Library, with a city of Los Angeles, a zip of 90077, and a country of the US.
INSERT INTO Customers (Site, Contact, Address, City, Zip, Country)
VALUES ('Huffington Post','Arianna Huffington','The Library','Los Angeles','90077','US');
The selection from the “Customers” table will now look like this, after the ID record incremented on its own, taking the next available integer:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US
As mentioned, you can also 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 row, but only insert data in the Site, Contact, and Country columns:
INSERT INTO Customers (Site, Contact, Country)
VALUES ('Spotify', 'Daniel Elk', 'SE');
The selection from the “Customers” table will now look like this:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US
7 Spotify Daniel Elk SE
Overall, adding rows to SQL tables is really straight forward. I mean, you’re not replacing anything… Yet…