select into a table with different column names

sql sqlite sql-insert
zolio picture zolio · May 12, 2012 · Viewed 53.8k times · Source

In SQL, Select into ... copies rows into a different (backup) table. Is this possible if the backup table has different structure (or different column names)? If not, what is the best way to achieve this?

Here is what I want to do: TableA has columns a1,a2,a3. I want to copy some rows from this table to another table TableB which has column b1,b2,b3,b4. Content of a1 to go into b1, a2 to b2 etc.

Answer

Erwin Brandstetter picture Erwin Brandstetter · May 12, 2012

The column names do not matter at all, as long the data types match.

If the data types of the columns don't match, try casting the values accordingly. Just try with small dummy tables. Be sure to list the target columns explicitly to avoid confusion. Like this:

INSERT INTO TableB (b1, b2, b3)
SELECT a1, a2, a3
FROM   TableA
WHERE <some condition>;

More details in the SQLite manual here.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More