SQL Insert

This tutorial will demonstrate how to INSERT records into a database table.

The SQL INSERT statement is used to insert records into a table in the database. There are essentially two ways we can insert data into a table.

  1. SQL INSERT INTO VALUE EXAMPLE
  2. SQL INSERT INTO SELECT EXAMPLE

Below is an example of each SQL INSERT query. Ensure that you understand how each approach works before moving on to the next tutorial.

SQL INSERT INTO VALUE EXAMPLE

SQL INSERT INTO VALUE Syntax

INSERT INTO “table_name” (“column1″, “column2″)
VALUES (“value1″, “value2″)

To demonstrate the above example assume that we have the following table called Games

ID Title Release Date Genres Platform
1001 GTA IV April 29, 2008 Action-adventure PS3,XBOX360
1002 Gran Turismo 5 November 24, 2010 Racing PS3
1003 Assassin’s Creed November 14, 2007 Third person PS3,XBOX360
1004 Battlefield: Bad Company June 26, 2008 First person shooter PS3,XBOX360
1005 LittleBigPlanet October 01, 2008 Puzzle platformer PS3

Lets add a new game into the Games table above using this query:

INSERT INTO Games (“ID”, “Title”, “Release Date”, “Genres”, “Platform”)
VALUES (“1006″, “Fifa 11″, “October, 1 2010″, “Sports”, “PS3,XBOX360,WII”)

Once executed the resultset would look like this:

ID Title Release Date Genres Platform
1001 GTA IV April 29, 2008 Action-adventure PS3,XBOX360
1002 Gran Turismo 5 November 24, 2010 Racing PS3
1003 Assassin’s Creed November 14, 2007 Third person PS3,XBOX360
1004 Battlefield: Bad Company June 26, 2008 First person shooter PS3,XBOX360
1005 LittleBigPlanet October 01, 2008 Puzzle platformer PS3
1006 Fifa 11 October 01, 2010 Sports PS3,XBOX360,WII

SQL INSERT INTO SELECT EXAMPLE

SQL INSERT INTO SELECT Syntax

INSERT INTO “table_name” (“column1″, “column2″)
SELECT “column3″, “column4″
FROM “table2″

To demonstrate the above example assume that we have the following tables Games and NewGames. Not the NewGames table contains a lot more columns than the Games table. In the insert query we are going to select only the columns that we require.

ID Title Release Date Genres Platform
1001 GTA IV April 29, 2008 Action-adventure PS3,XBOX360
1002 Gran Turismo 5 November 24, 2010 Racing PS3
1003 Assassin’s Creed November 14, 2007 Third person PS3,XBOX360
1004 Battlefield: Bad Company June 26, 2008 First person shooter PS3,XBOX360
1005 LittleBigPlanet October 01, 2008 Puzzle platformer PS3
1006 Fifa 11 October 01, 2010 Sports PS3,XBOX360,WII
ID Title Release Date Genres Platform Price Postage Quantity
1007 LittleBigPlanet 2 January 21, 2011 Adventure PS3,XBOX360 49.99 2.49 150
1008 Dead Space 2 January 28, 2011 Third person PS3,XBOX360 44.99 0.00 15
1009 Mindjack January 28, 2011 Third person XBOX360 39.99 2.49 100
1010 Mario Sports Mix Febuary 04, 2011 Adventure WII 39.99 0.00 50

Lets add a few more new games into the Games table above using this query:

INSERT INTO Games (“ID”, “Title”, “Release Date”, “Genres”, “Platform”)
SELECT “ID”, “Title”, “Release Date”, “Genres”, “Platform”
FROM NewGames

Once executed the resultset would look like this:

ID Title Release Date Genres Platform
1001 GTA IV April 29, 2008 Action-adventure PS3,XBOX360
1002 Gran Turismo 5 November 24, 2010 Racing PS3
1003 Assassin’s Creed November 14, 2007 Third person PS3,XBOX360
1004 Battlefield: Bad Company June 26, 2008 First person shooter PS3,XBOX360
1005 LittleBigPlanet October 01, 2008 Puzzle platformer PS3
1006 Fifa 11 October 01, 2010 Sports PS3,XBOX360,WII
1007 LittleBigPlanet 2 January 21, 2011 Adventure PS3,XBOX360
1008 Dead Space 2 January 28, 2011 Third person PS3,XBOX360
1009 Mindjack January 28, 2011 Third person XBOX360
1010 Mario Sports Mix Febuary 04, 2011 Adventure WII

How cool is that! Give it a go and once you feel comfortable using the SQL INSERT keyword move on to our next tutorial SQL UPDATE.