SQL: copy data from one table to another while adding additional columns of fixed values

Question

I want to copy two columns from table 1 into table 2, filling another two columns in table 2 with fixed strings. Here's what I've attempted:

table1 has columns: name, age, child, adult
table2 has columns: name, param, xvalue, yvalue

INSERT INTO table2 (name, param, xvalue, yvalue) 
  VALUES ('John', 'age', 
      SELECT child FROM table1 WHERE name='John',
      SELECT adult FROM table1 WHERE name='John');

But it gives a missing expression error.

The result should have table2 name column be John for all rows, and likewise the param column should be age for all rows. Table 2 xvalue and yvalue columns should contain the selected data from table 1.

Any idea how to do this?

Gordon Linoff · Accepted Answer · 2015-07-02 20:03:11Z

2

Use insert . . . select:

INSERT INTO table2 (name, param, xvalue, yvalue) 
     SELECT 'John', 'age', child, adult
     FROM table1 
     WHERE name = 'John';

answered Jul 2, 2015 at 20:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL: copy data from one table to another while adding additional columns of fixed values

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