Difference between revisions of "INSERT .. VALUES"

From SQLZOO
Jump to: navigation, search
(One intermediate revision by one user not shown)
Line 1: Line 1:
  
<h1>INSERT</h1>
+
CREATE TABLE USERS
  <p>The INSERT command is used to add a new row to a table.</p>
+
(
  <pre>INSERT INTO games(yr, city) VALUES (2012,'London')</pre>
+
   UserId INT NOT NULL AUTO_INCREMENT,
  <p>The table is <code>games</code><br/>
+
   Fname VARCHAR (255),
  The column names are <code>yr</code> and <code>city</code><br/>
+
  Mname VARCHAR (255),
  Strings in the literal values must be quoted with single quotes.</p>
+
  LName VARCHAR (255),
<hr/>
+
  PRIMARY KEY (UserId)
<h2>Example</h2>
+
  )
  <table border='1' style=''>
+
  <caption>games</caption>
+
  <tr> <th>yr</th> <th>city</th> </tr>
+
  <tr> <td align='right'>2000</td> <td>Sydney</td> </tr>
+
  <tr> <td align='right'>2004</td> <td>Athens</td> </tr>
+
  <tr> <td align='right'>2008</td> <td>Beijing</td> </tr>
+
  </table>
+
<p>The table <code>games</code> shows the year and the city hosting
+
  the Olympic Games.</p>
+
<p>You want to add the next Olympic games, in the year 2012, which will be held in London.</p>
+
<hr/>
+
<div class='ht'>
+
<div class=params>schema:scott</div>
+
<source lang=sql class='tidy'> DROP TABLE games</source>
+
<source lang=sql class='setup'> CREATE TABLE games(
+
   yr INTEGER PRIMARY KEY,
+
   city VARCHAR(20));
+
INSERT INTO games(yr,city) VALUES (2000,'Sydney');
+
INSERT INTO games(yr,city) VALUES (2004,'Athens');
+
INSERT INTO games(yr,city) VALUES (2008,'Beijing');
+
</source>
+
The INSERT statement adds a new row to the table:
+
<source lang='sql' class='def e-oracle'>
+
INSERT INTO scott.games(yr,city)  
+
VALUES (2012,'London');
+
SELECT * FROM scott.games;
+
</source>
+
<source lang='sql' class='def'>
+
INSERT INTO games(yr,city)
+
VALUES (2012,'London');
+
SELECT * FROM games;
+
</source>
+
</div>
+
  
<p>See also</p>
+
INSERT INTO USERS (Fname,Mname,Lname) VALUES ('Saad','A','Mian');
<ul>
+
  <li>[[INSERT .. SELECT]]
+
</ul>
+
 
+
<h2>What can go wrong</h2>
+
<div class='ht'>
+
Your INSERT statement may break some database rule such as the unique key requirement.
+
In this example there is a primary key on year - that means that there may not be two rows with the same year.
+
If you attempt to add a second row with 2008 for yr then you will get an error.
+
<source lang='sql' class='def e-oracle'>
+
INSERT INTO scott.games(yr,city)  
+
VALUES (2008,'Paris');
+
SELECT * FROM scott.games;
+
</source>
+
<source lang='sql' class='def'>
+
INSERT INTO games(yr,city)
+
VALUES (2008,'Paris');
+
SELECT * FROM games;
+
</source>
+

Revision as of 18:34, 21 February 2014

CREATE TABLE USERS (

 UserId INT NOT NULL AUTO_INCREMENT,
 Fname VARCHAR (255),
 Mname VARCHAR (255),
 LName VARCHAR (255),
 PRIMARY KEY (UserId)
 )

INSERT INTO USERS (Fname,Mname,Lname) VALUES ('Saad','A','Mian');

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense