How to Insert Data with Selective Column Selection in SQL's INSERT INTO ... SELECT Statement?

SQL's INSERT INTO ... SELECT: Targeted Data Insertion

SQL's powerful INSERT INTO ... SELECT statement lets you populate a table with data from a query's results. This is ideal for efficiently copying or transferring data between tables.

Let's examine a typical query:

<code class="language-sql">INSERT INTO courses (name, location, gid) 
SELECT name, location, gid 
FROM courses 
WHERE cid = $cid</code>

This copies entire rows. However, you often need to insert only specific columns, or assign custom values to certain fields.

Selective Column Insertion and Constant Values

To insert chosen columns and set a column like 'gid' to a fixed value, adjust the query like this:

<code class="language-sql">INSERT INTO courses (name, location, gid)
SELECT name, location, 1
FROM   courses
WHERE  cid = $cid</code>

Here, 'gid' is assigned the constant '1'. Remember to use a constant compatible with the 'gid' column's data type.

Important: The number of columns in the SELECT statement must match the number of columns listed in the INSERT statement.

The above is the detailed content of How to Insert Data with Selective Column Selection in SQL's INSERT INTO ... SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!