SQL Insert Into Select

Last modified on: December 3, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">&nbsp;</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="bda22da8-1642-486b-b602-c031ca51222a" data-message-model-slug="gpt-4o">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3><strong>SQL INSERT INTO SELECT: A Complete Guide</strong></h3>
<h4><strong>What is SQL INSERT INTO SELECT?</strong></h4>
<p>The <strong>SQL INSERT INTO SELECT</strong> statement is used to copy data from one table into another table. Unlike <code>SELECT INTO</code>, this does not create a new table. Instead, it inserts the selected data into an already existing table.</p>
<hr />
<h3><strong>Syntax of SQL INSERT INTO SELECT</strong></h3>
<h4><strong>Basic Syntax</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li><strong><code>target_table</code></strong>: The table into which data is inserted.</li>
<li><strong><code>source_table</code></strong>: The table from which data is selected.</li>
<li><strong><code>column1, column2, ...</code></strong>: Columns must match in number and data type between <code>target_table</code> and the <code>SELECT</code> query.</li>
</ul>
<hr />
<h3><strong>Key Features</strong></h3>
<ol>
<li><strong>Copies Data Between Tables:</strong> Moves data from one table to another efficiently.</li>
<li><strong>Does Not Create New Tables:</strong> Requires the target table to already exist.</li>
<li><strong>Selective Insertion:</strong> Use a <code>WHERE</code> clause to filter rows before insertion.</li>
</ol>
<hr />
<h3><strong>Example of SQL INSERT INTO SELECT</strong></h3>
<h4><strong>Scenario: Copying Employee Data</strong></h4>
<p>You want to insert data from an <code>employees</code> table into an <code>employees_backup</code> table.</p>
<h4><strong>Table: employees</strong></h4>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department</th>
<th>salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>John Smith</td>
<td>IT</td>
<td>50000</td>
</tr>
<tr>
<td>2</td>
<td>Jane Doe</td>
<td>HR</td>
<td>60000</td>
</tr>
<tr>
<td>3</td>
<td>Mike Johnson</td>
<td>IT</td>
<td>70000</td>
</tr>
</tbody>
</table>
<h4><strong>Table: employees_backup</strong></h4>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department</th>
<th>salary</th>
</tr>
</thead>
</table>
<h4><strong>Query:</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees_backup (employee_id, employee_name, department, salary)
SELECT employee_id, employee_name, department, salary
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Result:</strong> Data from the <code>employees</code> table is inserted into <code>employees_backup</code>.</p>
<hr />
<h3><strong>Using INSERT INTO SELECT with Conditions</strong></h3>
<p>You can filter rows to be inserted using a <code>WHERE</code> clause.</p>
<h4><strong>Example: Copying IT Department Employees</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees_backup (employee_id, employee_name, department, salary)
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE department = 'IT';
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Result:</strong></p>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department</th>
<th>salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>John Smith</td>
<td>IT</td>
<td>50000</td>
</tr>
<tr>
<td>3</td>
<td>Mike Johnson</td>
<td>IT</td>
<td>70000</td>
</tr>
</tbody>
</table>
<hr />
<h3><strong>Inserting Data into a Table with Additional Columns</strong></h3>
<p>If the target table has more columns than the source table, you can insert specific columns and set default or static values for others.</p>
<h4><strong>Example: Adding a Timestamp</strong></h4>
<h4><strong>Table: employees_backup</strong></h4>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department</th>
<th>salary</th>
<th>created_at</th>
</tr>
</thead>
</table>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees_backup (employee_id, employee_name, department, salary, created_at)
SELECT employee_id, employee_name, department, salary, GETDATE()
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Result:</strong></p>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department</th>
<th>salary</th>
<th>created_at</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>John Smith</td>
<td>IT</td>
<td>50000</td>
<td>2024-12-04 14:32:01</td>
</tr>
<tr>
<td>2</td>
<td>Jane Doe</td>
<td>HR</td>
<td>60000</td>
<td>2024-12-04 14:32:01</td>
</tr>
</tbody>
</table>
<hr />
<h3><strong>Using INSERT INTO SELECT for Joining Tables</strong></h3>
<p>You can combine data from multiple tables using a <code>JOIN</code> in the <code>SELECT</code> statement.</p>
<h4><strong>Example: Merging Employee and Order Data</strong></h4>
<h4><strong>Table: orders</strong></h4>
<table>
<thead>
<tr>
<th>order_id</th>
<th>employee_id</th>
<th>order_date</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>1</td>
<td>2024-01-01</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
<td>2024-01-02</td>
</tr>
</tbody>
</table>
<h4><strong>Query:</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees_backup (employee_id, employee_name, department, salary)
SELECT e.employee_id, e.employee_name, e.department, e.salary
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Result:</strong> Only employees with matching orders are inserted into <code>employees_backup</code>.</p>
<hr />
<h3><strong>Handling Duplicate Data</strong></h3>
<p>To prevent duplicate data insertion, use conditions like <code>NOT IN</code> or <code>NOT EXISTS</code>.</p>
<h4><strong>Example: Avoiding Duplicate Records</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees_backup (employee_id, employee_name, department, salary)
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM employees_backup
);
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Key Points to Remember</strong></h3>
<ol>
<li><strong>Matching Columns:</strong> Ensure the columns in the <code>INSERT INTO</code> and <code>SELECT</code> statements match in order, count, and data type.</li>
<li><strong>Target Table Exists:</strong> The target table must already exist.</li>
<li><strong>Use Static Values if Needed:</strong> You can add static or computed values during insertion.</li>
<li><strong>Prevent Duplicates:</strong> Use <code>WHERE</code>, <code>NOT IN</code>, or <code>NOT EXISTS</code> to avoid duplicate data.</li>
</ol>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More