Create table from query results

Question

I would very much appreciate an opinion about the best way to create a new table that will be a subset of an an existing table (based on the results of a query).

The new table is based on selecting only the specific records that contain any one of the 6 criteria (as shown below) for the first 2 characters of a selected field (field_name). All of the columns are required.

Here is my initial query draft.

CREATE TABLE table_name_update
AS (SELECT *
     FROM table_name
     WHERE field_name LIKE ‘A7%
     OR A8%
     OR D0%
     OR D1%
     OR D3%
     OR E0%

Any comments or suggestions from the experts? Thanks much.

Please explain why you want to do this. Wouldn't a VIEW be more more appropriate (as it avoids data duplication). — Dai
– Dai, Commented Jul 17, 2013 at 1:55

sgeddes · Accepted Answer · 2013-07-17 01:55:52Z

1

This depends slightly on which RDBMS you are using, but one option is SELECT INTO:

SELECT * INTO table_name_update
FROM table_name 
WHERE field_name LIKE 'A7%' OR
    field_name LIKE 'A8%' OR 
    field_name LIKE 'D0%' OR 
    field_name LIKE 'D1%' OR 
    field_name LIKE 'D3%' OR 
    field_name LIKE 'E0%'

If using Oracle, use CREATE TABLE AS:

CREATE TABLE table_name_update AS
SELECT * INTO table_name_update
FROM table_name 
WHERE field_name LIKE 'A7%' OR
    field_name LIKE 'A8%' OR 
    field_name LIKE 'D0%' OR 
    field_name LIKE 'D1%' OR 
    field_name LIKE 'D3%' OR 
    field_name LIKE 'E0%'

Please notice, I've also updated your logic to use single quotes and multiple LIKE statements.

answered Jul 17, 2013 at 1:55

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create table from query results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related