CODEWITHSUNDEEP
mysqlsqldatabasesql-insert
M. Shahzaib
M. Shahzaib

Reputation: 33

How to copy one table into another with some additional Data

I want to copy columns from table1 into table2 with some additonal columns in table2.

Although I know the syntax:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

But how can I add an additional column's data into table2?

I've tried this approach but it gives syntax error:

// first storing the desired data from table1 into a temporary table
CREATE TEMPORARY TABLE temp_table 
select column1, column2, column3 from table1 
where condition;

// then placing the selected columns into table2
INSERT INTO table2 (col1, col2, col3,col4) values (
SELECT column1 FROM temp_table, 
SELECT column2 FROM temp_table, 
SELECT column3 FROM temp_table, 
'Additional Value'
);

Upvotes: 0

Views: 52

Answers (2)

Vikram Jain
Vikram Jain

Reputation: 5588

Please, follow below query and you can use alias as extra column it's static or expression field: (SQL-Fiddle)

INSERT INTO table2 (col1, col2, col3, col4)
 SELECT column1, column2, column3, 'Additional Value' as column4
 FROM table1
 WHERE condition;

Upvotes: 1

GMB
GMB

Reputation: 222672

You can add a litteral string (or any other expression) to the column list. Consider:

INSERT INTO table2 (col1, col2, col3,col4)
SELECT
    column1,
    column2,
    column3,
    'Additional Value'
FROM table1

Upvotes: 2

Related Questions