1

I'm trying to copy data from one table to another.

For that matter I'm using this:

INSERT INTO radacct_2011_2012 SELECT 'RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay' from radacct where 'AcctStartTime' >= '2011' AND 'AcctStartTime' <= '2012';

When I try to run it I get the following error:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

I'm reading about it and none of the solutions that I found helped me.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
Igor Karolesky
  • 75
  • 1
  • 8

2 Answers2

3

You only SELECT one value because you escape the whole column list with '. So MySQL interpret this as a string value, not a column list.

So you should use the following query instead:

INSERT INTO `radacct_2011_2012` 
SELECT `RadAcctId`, `AcctSessionId`, `AcctUniqueId`, `UserName`, `Realm`, `NASIPAddress`, `NASPortId`, `NASPortType`, `AcctStartTime`, `AcctStopTime`, `AcctSessionTime`, `AcctAuthentic`, `ConnectInfo_start`, `ConnectInfo_stop`, `AcctInputOctets`, `AcctOutputOctets`, `CalledStationId`, `CallingStationId`, `AcctTerminateCause`, `ServiceType`, `FramedProtocol`, `FramedIPAddress`, `AcctStartDelay`, `AcctStopDelay` 
    FROM `radacct` 
    WHERE `AcctStartTime` >= '2011' AND `AcctStartTime` <= '2012';

There is a general problem on your query using the ' in the wrong situations. You are using ' on column names of the WHERE part too. MySQL compares 'AcctStartTime' >= '2011' as false because the string values (in this example AcctStartTime and 2011) are never equal. If you want to escape column names you have to use the backtick (``).

You should read When to use single quotes, double quotes, and backticks in MySQL to get the difference between the different escape possibilities.

Tim provided a demo to confirm that double quotes (and single quotes too) can not be used to escape column names.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
  • 1
    You can confirm for yourself that double quotes cannot be used to escape column names in MySQL [in this demo](http://rextester.com/JAG30958). – Tim Biegeleisen Nov 22 '17 at 11:16
0

Please check the no of columns in the first table and check the no of columns in the second table.

You should also match the order of the columns. i.e.

Table 1
col1 col2 col3 col4

Table 2
col1 col2 col3 col4

insert into table1 select col1,col2,col3,col4 from table2 where col1 = '2';

It will give error if the order is not match or the no of column is greater or less in the first table.

Pirate
  • 2,886
  • 4
  • 24
  • 42