Mysql – Insert data from a source table into a destination table with more columns

insertMySQL

I want to use INSERT INTO SELECT * to insert data into a dest table from a source table, but I have some extra columns added to destination table. These columns are distributed around the table like 2 are at top and 2 are at bottom.

Also for insertion I can insert some default value like null to these columns.

Best Answer

SELECT * selects col1, col2, col3, as defined in table2, and if the selection doesn't match the target, it will fail.

the conclusion is to alter the selection:

INSERT INTO table1 
SELECT col1, col2, NULL as col_extra, 'default' as col_extra2, col3, [...] 
FROM table2

not tested, but should work

Related Solutions

Postgresql – INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

Remove parens around column list in select. This doesn't do what you think it does.

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Best Answer

Related Solutions

Postgresql – INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

Sql-server – Combine – Select Into, with Insert Into Select

Related Question