SQL: Creating Databases and Tables - Insert

---

SQL: Creating Databases and Tables - Insert

INSERT

When you create a new table, it does not have any data. The first thing you often do is to insert new rows into the table.

SQL provides the INSERT statement that allows you to insert one or more rows into a table at a time

Syntax:

-- INSERT 
INSERT INTO table(column1, column2, ...)
VALUES (value1, value2, ...);
-- INSERT data that comes from another table
INSERT INTO table
SELECT column1, column2, ...
FROM another_table
WHERE condition;

Example: Link

-- Create Table
CREATE TABLE link(
ID serial PRIMARY KEY,
url VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
description VARCHAR(255),
rel VARCHAR (50)
);

## NULL

-- Insert Google
INSERT INTO link(url,name)
VALUES ('www.google.com', 'Google');

## data frame with 0 columns and 0 rows

Example: Link #2 Insert

-- INSERT Yahoo
INSERT INTO link(url,name)
VALUES ('www.yahoo.com', 'Yahoo');

## data frame with 0 columns and 0 rows

Example: Link #3 Insert Multiple Values

-- INSERT multiple values
INSERT INTO link(url,name)
VALUES ('www.bing.com', 'Bing'), ('www.amazon.com', 'Amazon');

## data frame with 0 columns and 0 rows

-- Select
SELECT * FROM link;

##   id            url   name description  rel
## 1  1 www.google.com Google        <NA> <NA>
## 2  2 www.google.com Google        <NA> <NA>
## 3  3  www.yahoo.com  Yahoo        <NA> <NA>
## 4  4   www.bing.com   Bing        <NA> <NA>
## 5  5 www.amazon.com Amazon        <NA> <NA>

Example: Insert another table

-- Create Table similar to link
CREATE TABLE link_copy (LIKE link);

## data frame with 0 columns and 0 rows

-- Insert into
INSERT INTO link_copy
SELECT * FROM link
WHERE name = 'Bing';

## data frame with 0 columns and 0 rows

-- Select
SELECT *
FROM link_copy;

##   id          url name description  rel
## 1  4 www.bing.com Bing        <NA> <NA>
## 2  4 www.bing.com Bing        <NA> <NA>