1

Say I have a table (lets call it DB10) which has 10 columns. I have another table with 15 columns (DB15), 10 of which are identical to the columns in DB10 in terms of name and data type.

I want to insert all the columns in DB10 into DB15. I have tried using the following:

INSERT INTO DB15 SELECT * FROM DB10

But this gives me Error Code 1136 "Column count doesn't match value count at row 1". From what I understand through research is that its trying to map 10 values to 15 columns.

So my question is, how is this possible without having to enter the individual columns. Can I perhaps use a function to make it assume a value of null for columns that can't be mapped?

The reason I want to do this? I have more than 10 columns to be mapped, and I just want to simplify the SQL script, smaller scripts are easier to check.

I've done my research and found this question here: INSERT into Table On Columns That Exist

It seems like the guy wants essentially the same thing done, but he 's trying to perform it on multiple linked tables and arrays, I couldn't follow how I could apply it to my case.

Community
  • 1
  • 1
Ali Mazaal
  • 11
  • 2
  • "I have a database which has 10 columns". A database can contain *tables*, which can contain columns. – Pere Sep 15 '14 at 07:19

2 Answers2

1

No it is not possible. You have to enter all the column names.

Namphibian
  • 12,046
  • 7
  • 46
  • 76
0

As a matter of fact it's best practice tot list columns in INSERT and SELECT statements.

However it's possible to generate the list programmaticly:

<?php
$fields = array();
$cResult = $sDb->query("SHOW COLUMNS IN DB10");
while ($columns = $cResult->fetch_row()) {
    $fields[] = $columns[0];
}
$sDb->query("INSERT INTO DB15 (" . implode(',', $fields) . ") SELECT " . 
           implode(',', $fields) . " FROM DB10"); 
}

Assuming remaining columns in DB15 are either nullable or have a default assigned

Gervs
  • 1,397
  • 9
  • 8
  • I don think that t is best practice it is the only syntactically correct way of doing it. – Namphibian Sep 15 '14 at 21:10
  • Whats with the weird syntax? Is this sql language? I'm using MySQL Workbench and I get red crosses next to almost all the rows. – Ali Mazaal Sep 16 '14 at 07:26
  • It's php syntax, not sql. – Gervs Sep 16 '14 at 07:44
  • Okay so it can't be done on MySQL Workbench? What do I need, I'd prefer to get it done on the existing software if possible. I did specify MySQL in the title but for some reason a mod edited it out.. – Ali Mazaal Sep 16 '14 at 12:01
  • Yes it can be done in workbench, but then you need to enter the individual columns in the query – Gervs Sep 16 '14 at 13:09