33

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
zolio
  • 2,439
  • 4
  • 22
  • 34
  • Which flavour of SQL? The only one I can find with a `SELECT..INTO` is MS-SQL, and the documentation for it implies it will create a new table - which should always have the "correct" structure. – millimoose May 12 '12 at 01:53
  • Thank you for the point. I am working on sqlite. I was not sure if "Select into... " works in sqlite. I tried just now, it is NOT working in sqlite. However the "Insert into.. " works for this. – zolio May 12 '12 at 04:44

1 Answers1

63

The column names do not matter at all, as long as data types match (or can be cast in the assignment).

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • cant we do this without specifying the column names like this ... INSERT INTO payment_reject_reasons SELECT * FROM `de_list_reasons` this gives me a error ... – mahen3d Sep 21 '12 at 05:27
  • 1
    @user1179459: You *can* do it if the row types match. If you get an error, they obviously don't. Also this form is only good for ad-hoc commands and special situations. Normally you should spell out the involved columns, so the command will not break if you add a column to one of the tables. I added a link to the manual. – Erwin Brandstetter Sep 21 '12 at 08:41