Google Git
Sign in
apache / spark-website / asf-site / . / site / docs / 3.5.3 / sql-ref-syntax-dml-insert-table.html
blob: 6772e79ae024e2be0a858d703684de23c79b888b [file] [log] [blame]
<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>INSERT TABLE - Spark 3.5.3 Documentation</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=DM+Sans:ital,wght@0,400;0,500;0,700;1,400;1,500;1,700&Courier+Prime:wght@400;700&display=swap" rel="stylesheet">
<link href="css/custom.css" rel="stylesheet">
<script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script>
<link rel="stylesheet" href="css/pygments-default.css">
<link rel="stylesheet" href="css/docsearch.min.css" />
<link rel="stylesheet" href="css/docsearch.css">
<!-- Matomo -->
<script>
var _paq = window._paq = window._paq || [];
/* tracker methods like "setCustomDimension" should be called before "trackPageView" */
_paq.push(["disableCookies"]);
_paq.push(['trackPageView']);
_paq.push(['enableLinkTracking']);
(function() {
var u="https://analytics.apache.org/";
_paq.push(['setTrackerUrl', u+'matomo.php']);
_paq.push(['setSiteId', '40']);
var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
g.async=true; g.src=u+'matomo.js'; s.parentNode.insertBefore(g,s);
})();
</script>
<!-- End Matomo Code -->
</head>
<body class="global">
<!--[if lt IE 7]>
<p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p>
<![endif]-->
<!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->
<nav class="navbar navbar-expand-lg navbar-dark p-0 px-4 fixed-top" style="background: #1d6890;" id="topbar">
<div class="navbar-brand"><a href="index.html">
<img src="img/spark-logo-rev.svg" width="141" height="72"/></a><span class="version">3.5.3</span>
</div>
<button class="navbar-toggler" type="button" data-toggle="collapse"
data-target="#navbarCollapse" aria-controls="navbarCollapse"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarCollapse">
<ul class="navbar-nav me-auto">
<li class="nav-item"><a href="index.html" class="nav-link">Overview</a></li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarQuickStart" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Programming Guides</a>
<div class="dropdown-menu" aria-labelledby="navbarQuickStart">
<a class="dropdown-item" href="quick-start.html">Quick Start</a>
<a class="dropdown-item" href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a>
<a class="dropdown-item" href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a>
<a class="dropdown-item" href="structured-streaming-programming-guide.html">Structured Streaming</a>
<a class="dropdown-item" href="streaming-programming-guide.html">Spark Streaming (DStreams)</a>
<a class="dropdown-item" href="ml-guide.html">MLlib (Machine Learning)</a>
<a class="dropdown-item" href="graphx-programming-guide.html">GraphX (Graph Processing)</a>
<a class="dropdown-item" href="sparkr.html">SparkR (R on Spark)</a>
<a class="dropdown-item" href="api/python/getting_started/index.html">PySpark (Python on Spark)</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarAPIDocs" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">API Docs</a>
<div class="dropdown-menu" aria-labelledby="navbarAPIDocs">
<a class="dropdown-item" href="api/scala/org/apache/spark/index.html">Scala</a>
<a class="dropdown-item" href="api/java/index.html">Java</a>
<a class="dropdown-item" href="api/python/index.html">Python</a>
<a class="dropdown-item" href="api/R/index.html">R</a>
<a class="dropdown-item" href="api/sql/index.html">SQL, Built-in Functions</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarDeploying" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Deploying</a>
<div class="dropdown-menu" aria-labelledby="navbarDeploying">
<a class="dropdown-item" href="cluster-overview.html">Overview</a>
<a class="dropdown-item" href="submitting-applications.html">Submitting Applications</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="spark-standalone.html">Spark Standalone</a>
<a class="dropdown-item" href="running-on-mesos.html">Mesos</a>
<a class="dropdown-item" href="running-on-yarn.html">YARN</a>
<a class="dropdown-item" href="running-on-kubernetes.html">Kubernetes</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarMore" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">More</a>
<div class="dropdown-menu" aria-labelledby="navbarMore">
<a class="dropdown-item" href="configuration.html">Configuration</a>
<a class="dropdown-item" href="monitoring.html">Monitoring</a>
<a class="dropdown-item" href="tuning.html">Tuning Guide</a>
<a class="dropdown-item" href="job-scheduling.html">Job Scheduling</a>
<a class="dropdown-item" href="security.html">Security</a>
<a class="dropdown-item" href="hardware-provisioning.html">Hardware Provisioning</a>
<a class="dropdown-item" href="migration-guide.html">Migration Guide</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="building-spark.html">Building Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/contributing.html">Contributing to Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a>
</div>
</li>
<li class="nav-item">
<input type="text" id="docsearch-input" placeholder="Search the docs…">
</li>
</ul>
<!--<span class="navbar-text navbar-right"><span class="version-text">v3.5.3</span></span>-->
</div>
</nav>
<div class="container">
<div class="left-menu-wrapper">
<div class="left-menu">
<h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3>
<ul>
<li>
<a href="sql-getting-started.html">
Getting Started
</a>
</li>
<li>
<a href="sql-data-sources.html">
Data Sources
</a>
</li>
<li>
<a href="sql-performance-tuning.html">
Performance Tuning
</a>
</li>
<li>
<a href="sql-distributed-sql-engine.html">
Distributed SQL Engine
</a>
</li>
<li>
<a href="sql-pyspark-pandas-with-arrow.html">
PySpark Usage Guide for Pandas with Apache Arrow
</a>
</li>
<li>
<a href="sql-migration-guide.html">
Migration Guide
</a>
</li>
<li>
<a href="sql-ref.html">
SQL Reference
</a>
</li>
<ul>
<li>
<a href="sql-ref-ansi-compliance.html">
ANSI Compliance
</a>
</li>
<li>
<a href="sql-ref-datatypes.html">
Data Types
</a>
</li>
<li>
<a href="sql-ref-datetime-pattern.html">
Datetime Pattern
</a>
</li>
<li>
<a href="sql-ref-number-pattern.html">
Number Pattern
</a>
</li>
<li>
<a href="sql-ref-functions.html">
Functions
</a>
</li>
<li>
<a href="sql-ref-identifier.html">
Identifiers
</a>
</li>
<li>
<a href="sql-ref-literals.html">
Literals
</a>
</li>
<li>
<a href="sql-ref-null-semantics.html">
Null Semantics
</a>
</li>
<li>
<a href="sql-ref-syntax.html">
SQL Syntax
</a>
</li>
<ul>
<li>
<a href="sql-ref-syntax.html#ddl-statements">
Data Definition Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#dml-statements">
Data Manipulation Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#data-retrieval-statements">
Data Retrieval(Queries)
</a>
</li>
<li>
<a href="sql-ref-syntax.html#auxiliary-statements">
Auxiliary Statements
</a>
</li>
</ul>
</ul>
<li>
<a href="sql-error-conditions.html">
Error Conditions
</a>
</li>
</ul>
</div>
</div>
<input id="nav-trigger" class="nav-trigger" checked type="checkbox">
<label for="nav-trigger"></label>
<div class="content-with-sidebar mr-3" id="content">
<h1 class="title">INSERT TABLE</h1>
<h3 id="description">Description</h3>
<p>The <code class="language-plaintext highlighter-rouge">INSERT</code> statement inserts new rows into a table or overwrites the existing data in the table. The inserted rows can be specified by value expressions or result from a query.</p>
<h3 id="syntax">Syntax</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">INSERT</span> <span class="p">[</span> <span class="k">INTO</span> <span class="o">|</span> <span class="n">OVERWRITE</span> <span class="p">]</span> <span class="p">[</span> <span class="k">TABLE</span> <span class="p">]</span> <span class="n">table_identifier</span> <span class="p">[</span> <span class="n">partition_spec</span> <span class="p">]</span> <span class="p">[</span> <span class="p">(</span> <span class="n">column_list</span> <span class="p">)</span> <span class="p">]</span>
<span class="err">{</span> <span class="k">VALUES</span> <span class="p">(</span> <span class="err">{</span> <span class="n">value</span> <span class="o">|</span> <span class="k">NULL</span> <span class="err">}</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="p">)</span> <span class="p">[</span> <span class="p">,</span> <span class="p">(</span> <span class="p">...</span> <span class="p">)</span> <span class="p">]</span> <span class="o">|</span> <span class="n">query</span> <span class="err">}</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="p">[</span> <span class="k">TABLE</span> <span class="p">]</span> <span class="n">table_identifier</span> <span class="k">REPLACE</span> <span class="k">WHERE</span> <span class="n">boolean_expression</span> <span class="n">query</span>
</code></pre></div></div>
<h3 id="parameters">Parameters</h3>
<ul>
<li>
<p><strong>table_identifier</strong></p>
<p>Specifies a table name, which may be optionally qualified with a database name.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">[ database_name. ] table_name</code></p>
</li>
<li>
<p><strong>partition_spec</strong></p>
<p>An optional parameter that specifies a comma-separated list of key and value pairs
for partitions. Note that one can use a typed literal (e.g., date&#8217;2019-01-02&#8217;) in the partition spec.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">PARTITION ( partition_col_name = partition_col_val [ , ... ] )</code></p>
</li>
<li>
<p><strong>column_list</strong></p>
<p>An optional parameter that specifies a comma-separated list of columns belonging to the <code class="language-plaintext highlighter-rouge">table_identifier</code> table. Spark will reorder the columns of the input query to match the table schema according to the specified column list.</p>
<p><strong>Note:</strong>The current behaviour has some limitations:</p>
<ul>
<li>All specified columns should exist in the table and not be duplicated from each other. It includes all columns except the static partition columns.</li>
<li>The size of the column list should be exactly the size of the data from <code class="language-plaintext highlighter-rouge">VALUES</code> clause or query.</li>
</ul>
</li>
<li>
<p><strong>VALUES ( { value <code class="language-plaintext highlighter-rouge">|</code> NULL } [ , &#8230; ] ) [ , ( &#8230; ) ]</strong></p>
<p>Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted.
A comma must be used to separate each value in the clause. More than one set of values can be specified to insert multiple rows.</p>
</li>
<li>
<p><strong>boolean_expression</strong></p>
<p>Specifies any expression that evaluates to a result type <code class="language-plaintext highlighter-rouge">boolean</code>. Two or
more expressions may be combined together using the logical
operators ( <code class="language-plaintext highlighter-rouge">AND</code>, <code class="language-plaintext highlighter-rouge">OR</code> ).</p>
</li>
<li>
<p><strong>query</strong></p>
<p>A query that produces the rows to be inserted. It can be in one of following formats:</p>
<ul>
<li>a <a href="sql-ref-syntax-qry-select.html">SELECT</a> statement</li>
<li>a <a href="sql-ref-syntax-qry-select-inline-table.html">Inline Table</a> statement</li>
<li>a <code class="language-plaintext highlighter-rouge">FROM</code> statement</li>
</ul>
</li>
</ul>
<h3 id="examples">Examples</h3>
<h4 id="insert-into">Insert Into</h4>
<h5 id="single-row-insert-using-a-values-clause">Single Row Insert Using a VALUES Clause</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">students</span> <span class="p">(</span><span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">64</span><span class="p">),</span> <span class="n">address</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">64</span><span class="p">))</span>
<span class="k">USING</span> <span class="n">PARQUET</span> <span class="n">PARTITIONED</span> <span class="k">BY</span> <span class="p">(</span><span class="n">student_id</span> <span class="nb">INT</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Amy Smith'</span><span class="p">,</span> <span class="s1">'123 Park Ave, San Jose'</span><span class="p">,</span> <span class="mi">111111</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span><span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
</code></pre></div></div>
<h5 id="multi-row-insert-using-a-values-clause">Multi-Row Insert Using a VALUES Clause</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Bob Brown'</span><span class="p">,</span> <span class="s1">'456 Taylor St, Cupertino'</span><span class="p">,</span> <span class="mi">222222</span><span class="p">),</span>
<span class="p">(</span><span class="s1">'Cathy Johnson'</span><span class="p">,</span> <span class="s1">'789 Race Ave, Palo Alto'</span><span class="p">,</span> <span class="mi">333333</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+------------------------+----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+------------------------+----------+</span>
<span class="o">|</span> <span class="n">Bob</span> <span class="n">Brown</span><span class="o">|</span><span class="mi">456</span> <span class="n">Taylor</span> <span class="n">St</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+------------------------+----------+</span>
<span class="o">|</span><span class="n">Cathy</span> <span class="n">Johnson</span><span class="o">|</span> <span class="mi">789</span> <span class="n">Race</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Palo</span> <span class="n">Alto</span><span class="o">|</span> <span class="mi">333333</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------+-----------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-select-statement">Insert Using a SELECT Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the persons table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">persons</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">ssn</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span><span class="mi">123456789</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">Eddie</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">245</span> <span class="n">Market</span> <span class="n">St</span><span class="p">,</span> <span class="n">Milpitas</span><span class="o">|</span><span class="mi">345678901</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">student_id</span> <span class="o">=</span> <span class="mi">444444</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">address</span> <span class="k">FROM</span> <span class="n">persons</span> <span class="k">WHERE</span> <span class="n">name</span> <span class="o">=</span> <span class="nv">"Dora Williams"</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Bob</span> <span class="n">Brown</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Taylor</span> <span class="n">St</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Cathy</span> <span class="n">Johnson</span><span class="o">|</span> <span class="mi">789</span> <span class="n">Race</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Palo</span> <span class="n">Alto</span><span class="o">|</span> <span class="mi">333333</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span> <span class="mi">444444</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-table-statement">Insert Using a TABLE Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the visiting_students table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">visiting_students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span><span class="n">Fleur</span> <span class="n">Laurent</span><span class="o">|</span><span class="mi">345</span> <span class="n">Copper</span> <span class="n">St</span><span class="p">,</span> <span class="n">London</span><span class="o">|</span> <span class="mi">777777</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span><span class="n">Gordon</span> <span class="n">Martin</span><span class="o">|</span> <span class="mi">779</span> <span class="n">Lake</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Oxford</span><span class="o">|</span> <span class="mi">888888</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="k">TABLE</span> <span class="n">visiting_students</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Bob</span> <span class="n">Brown</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Taylor</span> <span class="n">St</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Cathy</span> <span class="n">Johnson</span><span class="o">|</span> <span class="mi">789</span> <span class="n">Race</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Palo</span> <span class="n">Alto</span><span class="o">|</span> <span class="mi">333333</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span> <span class="mi">444444</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Fleur</span> <span class="n">Laurent</span><span class="o">|</span> <span class="mi">345</span> <span class="n">Copper</span> <span class="n">St</span><span class="p">,</span> <span class="n">London</span><span class="o">|</span> <span class="mi">777777</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Gordon</span> <span class="n">Martin</span><span class="o">|</span> <span class="mi">779</span> <span class="n">Lake</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Oxford</span><span class="o">|</span> <span class="mi">888888</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-from-statement">Insert Using a FROM Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the applicants table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">applicants</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span><span class="n">qualified</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span><span class="n">Helen</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">469</span> <span class="n">Mission</span> <span class="n">St</span><span class="p">,</span> <span class="n">San</span> <span class="n">Diego</span><span class="o">|</span> <span class="mi">999999</span><span class="o">|</span> <span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span> <span class="n">Ivy</span> <span class="n">King</span><span class="o">|</span><span class="mi">367</span> <span class="n">Leigh</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Santa</span> <span class="n">Clara</span><span class="o">|</span> <span class="mi">101010</span><span class="o">|</span> <span class="k">false</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span> <span class="n">Jason</span> <span class="n">Wang</span><span class="o">|</span> <span class="mi">908</span> <span class="n">Bird</span> <span class="n">St</span><span class="p">,</span> <span class="n">Saratoga</span><span class="o">|</span> <span class="mi">121212</span><span class="o">|</span> <span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span>
<span class="k">FROM</span> <span class="n">applicants</span> <span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">address</span><span class="p">,</span> <span class="n">student_id</span> <span class="k">WHERE</span> <span class="n">qualified</span> <span class="o">=</span> <span class="k">true</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Bob</span> <span class="n">Brown</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Taylor</span> <span class="n">St</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Cathy</span> <span class="n">Johnson</span><span class="o">|</span> <span class="mi">789</span> <span class="n">Race</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Palo</span> <span class="n">Alto</span><span class="o">|</span> <span class="mi">333333</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span> <span class="mi">444444</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Fleur</span> <span class="n">Laurent</span><span class="o">|</span> <span class="mi">345</span> <span class="n">Copper</span> <span class="n">St</span><span class="p">,</span> <span class="n">London</span><span class="o">|</span> <span class="mi">777777</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Gordon</span> <span class="n">Martin</span><span class="o">|</span> <span class="mi">779</span> <span class="n">Lake</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Oxford</span><span class="o">|</span> <span class="mi">888888</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Helen</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">469</span> <span class="n">Mission</span> <span class="n">St</span><span class="p">,</span> <span class="n">San</span> <span class="n">Diego</span><span class="o">|</span> <span class="mi">999999</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Jason</span> <span class="n">Wang</span><span class="o">|</span> <span class="mi">908</span> <span class="n">Bird</span> <span class="n">St</span><span class="p">,</span> <span class="n">Saratoga</span><span class="o">|</span> <span class="mi">121212</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-typed-date-literal-for-a-partition-column-value">Insert Using a Typed Date Literal for a Partition Column Value</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">students</span> <span class="p">(</span><span class="n">name</span> <span class="n">STRING</span><span class="p">,</span> <span class="n">address</span> <span class="n">STRING</span><span class="p">)</span> <span class="n">PARTITIONED</span> <span class="k">BY</span> <span class="p">(</span><span class="n">birthday</span> <span class="nb">DATE</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">birthday</span> <span class="o">=</span> <span class="nb">date</span><span class="s1">'2019-01-02'</span><span class="p">)</span>
<span class="k">VALUES</span> <span class="p">(</span><span class="s1">'Amy Smith'</span><span class="p">,</span> <span class="s1">'123 Park Ave, San Jose'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+-------------------------+-----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">birthday</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+-------------------------+-----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">2019</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">02</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+-------------------------+-----------+</span>
</code></pre></div></div>
<h4 id="insert-with-a-column-list">Insert with a column list</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="p">(</span><span class="n">address</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">student_id</span><span class="p">)</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Hangzhou, China'</span><span class="p">,</span> <span class="s1">'Kent Yao'</span><span class="p">,</span> <span class="mi">11215016</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span> <span class="k">WHERE</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">'Kent Yao'</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Kent</span> <span class="n">Yao</span> <span class="o">|</span> <span class="n">Hangzhou</span><span class="p">,</span> <span class="n">China</span><span class="o">|</span> <span class="mi">11215016</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-with-both-a-partition-spec-and-a-column-list">Insert with both a partition spec and a column list</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">student_id</span> <span class="o">=</span> <span class="mi">11215017</span><span class="p">)</span> <span class="p">(</span><span class="n">address</span><span class="p">,</span> <span class="n">name</span><span class="p">)</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Hangzhou, China'</span><span class="p">,</span> <span class="s1">'Kent Yao Jr.'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span> <span class="k">WHERE</span> <span class="n">student_id</span> <span class="o">=</span> <span class="mi">11215017</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Kent</span> <span class="n">Yao</span> <span class="n">Jr</span><span class="p">.</span><span class="o">|</span> <span class="n">Hangzhou</span><span class="p">,</span> <span class="n">China</span><span class="o">|</span> <span class="mi">11215017</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+----------------------+----------+</span>
</code></pre></div></div>
<h4 id="insert-overwrite">Insert Overwrite</h4>
<h5 id="insert-using-a-values-clause">Insert Using a VALUES Clause</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the students table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">|</span> <span class="n">Bob</span> <span class="n">Brown</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Taylor</span> <span class="n">St</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">|</span><span class="n">Cathy</span> <span class="n">Johnson</span><span class="o">|</span> <span class="mi">789</span> <span class="n">Race</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Palo</span> <span class="n">Alto</span><span class="o">|</span> <span class="mi">333333</span><span class="o">|</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span> <span class="mi">444444</span><span class="o">|</span>
<span class="o">|</span><span class="n">Fleur</span> <span class="n">Laurent</span><span class="o">|</span> <span class="mi">345</span> <span class="n">Copper</span> <span class="n">St</span><span class="p">,</span> <span class="n">London</span><span class="o">|</span> <span class="mi">777777</span><span class="o">|</span>
<span class="o">|</span><span class="n">Gordon</span> <span class="n">Martin</span><span class="o">|</span> <span class="mi">779</span> <span class="n">Lake</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Oxford</span><span class="o">|</span> <span class="mi">888888</span><span class="o">|</span>
<span class="o">|</span> <span class="n">Helen</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">469</span> <span class="n">Mission</span> <span class="n">St</span><span class="p">,</span> <span class="n">San</span> <span class="n">Diego</span><span class="o">|</span> <span class="mi">999999</span><span class="o">|</span>
<span class="o">|</span> <span class="n">Jason</span> <span class="n">Wang</span><span class="o">|</span> <span class="mi">908</span> <span class="n">Bird</span> <span class="n">St</span><span class="p">,</span> <span class="n">Saratoga</span><span class="o">|</span> <span class="mi">121212</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Ashua Hill'</span><span class="p">,</span> <span class="s1">'456 Erica Ct, Cupertino'</span><span class="p">,</span> <span class="mi">111111</span><span class="p">),</span>
<span class="p">(</span><span class="s1">'Brian Reed'</span><span class="p">,</span> <span class="s1">'723 Kern Ave, Palo Alto'</span><span class="p">,</span> <span class="mi">222222</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+-----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+-----------------------+----------+</span>
<span class="o">|</span><span class="n">Ashua</span> <span class="n">Hill</span><span class="o">|</span><span class="mi">456</span> <span class="n">Erica</span> <span class="n">Ct</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">|</span><span class="n">Brian</span> <span class="n">Reed</span><span class="o">|</span><span class="mi">723</span> <span class="n">Kern</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Palo</span> <span class="n">Alto</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+-----------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-select-statement-1">Insert Using a SELECT Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the persons table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">persons</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">ssn</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span><span class="mi">123456789</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">Eddie</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">245</span> <span class="n">Market</span> <span class="n">St</span><span class="p">,</span> <span class="n">Milpitas</span><span class="o">|</span><span class="mi">345678901</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">student_id</span> <span class="o">=</span> <span class="mi">222222</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">address</span> <span class="k">FROM</span> <span class="n">persons</span> <span class="k">WHERE</span> <span class="n">name</span> <span class="o">=</span> <span class="nv">"Dora Williams"</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span> <span class="n">Ashua</span> <span class="n">Hill</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Erica</span> <span class="n">Ct</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span> <span class="mi">222222</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-replace-where-statement">Insert Using a REPLACE WHERE Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the persons and persons2 table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">persons</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">ssn</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span><span class="n">Dora</span> <span class="n">Williams</span><span class="o">|</span><span class="mi">134</span> <span class="n">Forest</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Menlo</span> <span class="n">Park</span><span class="o">|</span><span class="mi">123456789</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">Eddie</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">245</span> <span class="n">Market</span> <span class="n">St</span><span class="p">,</span> <span class="n">Milpitas</span><span class="o">|</span><span class="mi">345678901</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">persons2</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">ssn</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">Ashua</span> <span class="n">Hill</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Erica</span> <span class="n">Ct</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span><span class="mi">432795921</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="c1">-- in an atomic operation, 1) delete rows with ssn = 123456789 and 2) insert rows from persons2 </span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">persons</span> <span class="k">REPLACE</span> <span class="k">WHERE</span> <span class="n">ssn</span> <span class="o">=</span> <span class="mi">123456789</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">persons2</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">persons</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">ssn</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">Eddie</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">245</span> <span class="n">Market</span> <span class="n">St</span><span class="p">,</span> <span class="n">Milpitas</span><span class="o">|</span><span class="mi">345678901</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
<span class="o">|</span> <span class="n">Ashua</span> <span class="n">Hill</span><span class="o">|</span> <span class="mi">456</span> <span class="n">Erica</span> <span class="n">Ct</span><span class="p">,</span> <span class="n">Cupertino</span><span class="o">|</span><span class="mi">432795921</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+--------------------------+---------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-table-statement-1">Insert Using a TABLE Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the visiting_students table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">visiting_students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span><span class="n">Fleur</span> <span class="n">Laurent</span><span class="o">|</span><span class="mi">345</span> <span class="n">Copper</span> <span class="n">St</span><span class="p">,</span> <span class="n">London</span><span class="o">|</span> <span class="mi">777777</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span><span class="n">Gordon</span> <span class="n">Martin</span><span class="o">|</span> <span class="mi">779</span> <span class="n">Lake</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Oxford</span><span class="o">|</span> <span class="mi">888888</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span> <span class="k">TABLE</span> <span class="n">visiting_students</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span><span class="n">Fleur</span> <span class="n">Laurent</span><span class="o">|</span><span class="mi">345</span> <span class="n">Copper</span> <span class="n">St</span><span class="p">,</span> <span class="n">London</span><span class="o">|</span> <span class="mi">777777</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
<span class="o">|</span><span class="n">Gordon</span> <span class="n">Martin</span><span class="o">|</span> <span class="mi">779</span> <span class="n">Lake</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Oxford</span><span class="o">|</span> <span class="mi">888888</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+---------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-from-statement-1">Insert Using a FROM Statement</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Assuming the applicants table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">applicants</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span><span class="n">qualified</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span><span class="n">Helen</span> <span class="n">Davis</span><span class="o">|</span> <span class="mi">469</span> <span class="n">Mission</span> <span class="n">St</span><span class="p">,</span> <span class="n">San</span> <span class="n">Diego</span><span class="o">|</span> <span class="mi">999999</span><span class="o">|</span> <span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span> <span class="n">Ivy</span> <span class="n">King</span><span class="o">|</span><span class="mi">367</span> <span class="n">Leigh</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">Santa</span> <span class="n">Clara</span><span class="o">|</span> <span class="mi">101010</span><span class="o">|</span> <span class="k">false</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="o">|</span> <span class="n">Jason</span> <span class="n">Wang</span><span class="o">|</span> <span class="mi">908</span> <span class="n">Bird</span> <span class="n">St</span><span class="p">,</span> <span class="n">Saratoga</span><span class="o">|</span> <span class="mi">121212</span><span class="o">|</span> <span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+--------------------------+----------+---------+</span>
<span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span>
<span class="k">FROM</span> <span class="n">applicants</span> <span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">address</span><span class="p">,</span> <span class="n">student_id</span> <span class="k">WHERE</span> <span class="n">qualified</span> <span class="o">=</span> <span class="k">true</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+----------+</span>
<span class="o">|</span><span class="n">Helen</span> <span class="n">Davis</span><span class="o">|</span><span class="mi">469</span> <span class="n">Mission</span> <span class="n">St</span><span class="p">,</span> <span class="n">San</span> <span class="n">Diego</span><span class="o">|</span> <span class="mi">999999</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+----------+</span>
<span class="o">|</span> <span class="n">Jason</span> <span class="n">Wang</span><span class="o">|</span> <span class="mi">908</span> <span class="n">Bird</span> <span class="n">St</span><span class="p">,</span> <span class="n">Saratoga</span><span class="o">|</span> <span class="mi">121212</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-using-a-typed-date-literal-for-a-partition-column-value-1">Insert Using a Typed Date Literal for a Partition Column Value</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">students</span> <span class="p">(</span><span class="n">name</span> <span class="n">STRING</span><span class="p">,</span> <span class="n">address</span> <span class="n">STRING</span><span class="p">)</span> <span class="n">PARTITIONED</span> <span class="k">BY</span> <span class="p">(</span><span class="n">birthday</span> <span class="nb">DATE</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">birthday</span> <span class="o">=</span> <span class="nb">date</span><span class="s1">'2019-01-02'</span><span class="p">)</span>
<span class="k">VALUES</span> <span class="p">(</span><span class="s1">'Amy Smith'</span><span class="p">,</span> <span class="s1">'123 Park Ave, San Jose'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+-------------------------+-----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">birthday</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+-------------------------+-----------+</span>
<span class="o">|</span> <span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span> <span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">2019</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">02</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+-------------------------+-----------+</span>
<span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">birthday</span> <span class="o">=</span> <span class="nb">date</span><span class="s1">'2019-01-02'</span><span class="p">)</span>
<span class="k">VALUES</span><span class="p">(</span><span class="s1">'Jason Wang'</span><span class="p">,</span> <span class="s1">'908 Bird St, Saratoga'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+-----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span> <span class="n">birthday</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+-----------+</span>
<span class="o">|</span> <span class="n">Jason</span> <span class="n">Wang</span><span class="o">|</span> <span class="mi">908</span> <span class="n">Bird</span> <span class="n">St</span><span class="p">,</span> <span class="n">Saratoga</span><span class="o">|</span> <span class="mi">2019</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">02</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+-------------------------+-----------+</span>
</code></pre></div></div>
<h5 id="insert-with-a-column-list-1">Insert with a column list</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span> <span class="p">(</span><span class="n">address</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">student_id</span><span class="p">)</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Hangzhou, China'</span><span class="p">,</span> <span class="s1">'Kent Yao'</span><span class="p">,</span> <span class="mi">11215016</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span> <span class="k">WHERE</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">'Kent Yao'</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Kent</span> <span class="n">Yao</span> <span class="o">|</span> <span class="n">Hangzhou</span><span class="p">,</span> <span class="n">China</span><span class="o">|</span> <span class="mi">11215016</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
</code></pre></div></div>
<h5 id="insert-with-both-a-partition-spec-and-a-column-list-1">Insert with both a partition spec and a column list</h5>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">INSERT</span> <span class="n">OVERWRITE</span> <span class="n">students</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">student_id</span> <span class="o">=</span> <span class="mi">11215016</span><span class="p">)</span> <span class="p">(</span><span class="n">address</span><span class="p">,</span> <span class="n">name</span><span class="p">)</span> <span class="k">VALUES</span>
<span class="p">(</span><span class="s1">'Hangzhou, China'</span><span class="p">,</span> <span class="s1">'Kent Yao Jr.'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span> <span class="k">WHERE</span> <span class="n">student_id</span> <span class="o">=</span> <span class="mi">11215016</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Kent</span> <span class="n">Yao</span> <span class="n">Jr</span><span class="p">.</span><span class="o">|</span> <span class="n">Hangzhou</span><span class="p">,</span> <span class="n">China</span><span class="o">|</span> <span class="mi">11215016</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+----------------------+----------+</span>
</code></pre></div></div>
<h3 id="related-statements">Related Statements</h3>
<ul>
<li><a href="sql-ref-syntax-dml-insert-overwrite-directory.html">INSERT OVERWRITE DIRECTORY statement</a></li>
</ul>
</div>
<!-- /container -->
</div>
<script src="js/vendor/jquery-3.5.1.min.js"></script>
<script src="js/vendor/bootstrap.bundle.min.js"></script>
<script src="js/vendor/anchor.min.js"></script>
<script src="js/main.js"></script>
<script type="text/javascript" src="js/vendor/docsearch.min.js"></script>
<script type="text/javascript">
// DocSearch is entirely free and automated. DocSearch is built in two parts:
// 1. a crawler which we run on our own infrastructure every 24 hours. It follows every link
// in your website and extract content from every page it traverses. It then pushes this
// content to an Algolia index.
// 2. a JavaScript snippet to be inserted in your website that will bind this Algolia index
// to your search input and display its results in a dropdown UI. If you want to find more
// details on how works DocSearch, check the docs of DocSearch.
docsearch({
apiKey: 'd62f962a82bc9abb53471cb7b89da35e',
appId: 'RAI69RXRSK',
indexName: 'apache_spark',
inputSelector: '#docsearch-input',
enhancedSearchInput: true,
algoliaOptions: {
'facetFilters': ["version:3.5.3"]
},
debug: false // Set debug to true if you want to inspect the dropdown
});
</script>
<!-- MathJax Section -->
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
TeX: { equationNumbers: { autoNumber: "AMS" } }
});
</script>
<script>
// Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS.
// We could use "//cdn.mathjax...", but that won't support "file://".
(function(d, script) {
script = d.createElement('script');
script.type = 'text/javascript';
script.async = true;
script.onload = function(){
MathJax.Hub.Config({
tex2jax: {
inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ],
displayMath: [ ["$$","$$"], ["\\[", "\\]"] ],
processEscapes: true,
skipTags: ['script', 'noscript', 'style', 'textarea', 'pre']
}
});
};
script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') +
'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' +
'?config=TeX-AMS-MML_HTMLorMML';
d.getElementsByTagName('head')[0].appendChild(script);
}(document));
</script>
</body>
</html>