Mysql – Insert All Columns from one table into another table MYSQL

insertmergeMySQL

I am trying to insert multiple columns with their data from one table into another table, without a common column (i would expect a cross product in the result). I am not sure how that's possible, because each of the two tables have varied number of columns, which rules out UNION on them. I tried a Create..Select to create a 3rd table with all columns from both the tables but unsure about the syntax (keeps throwing syntax error).

Both Tables' syntax:

CREATE TABLE `t_modul` (
  `ID` int(11) NOT NULL,
  `Seriennummer` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(128) DEFAULT NULL,
  `StandortID` int(11) NOT NULL,
  `Firmwareversion` varchar(8) DEFAULT NULL,
  `Optionen` int(11) DEFAULT NULL,
  `Feldstärke` int(11) DEFAULT NULL,
  `Platziert` bit(1) DEFAULT NULL,
  `Sendeintervall` int(11) DEFAULT NULL,
  `Batteriekapazitaet` int(11) DEFAULT NULL,
  `Modulzusatzinfo` varchar(255) DEFAULT NULL,
  `ModulUserinfo` varchar(128) DEFAULT NULL,
  `ReadOutDate` varchar(40) DEFAULT NULL,
  `ReduktionsIntervall` int(11) DEFAULT NULL,
  `Startdatum` varchar(40) DEFAULT NULL,
  `SAMTemperaturEin` double DEFAULT NULL,
  `SAMTemperaturAus` double DEFAULT NULL,
  `SAMCoolingOff` int(11) DEFAULT NULL,
  `SAMCoolingOn` int(11) DEFAULT NULL,
  `SAMCoolingOffMax` double DEFAULT NULL,
  `SAMCoolingOnMin` double DEFAULT NULL,
  `SAMParameterField` int(11) DEFAULT NULL,
  `SAMIsAlarmMelder` bit(1) DEFAULT NULL,
  `SAMNv27` int(11) DEFAULT NULL,
  `Hardwarekonfiguration` int(11) DEFAULT NULL,
  `Anwendungskonfiguration` int(11) DEFAULT NULL,
  `Logkonfiguration` int(11) DEFAULT NULL,
  `Archivsendezeit_0` int(11) DEFAULT NULL,
  `Archivsendezeit_1` int(11) DEFAULT NULL,
  `IsLogger` bit(1) DEFAULT NULL,
  `Datenpunktkennung` varchar(128) DEFAULT NULL,
  `TTRFtpARCHPollDoneDate` varchar(40) DEFAULT NULL,
  `Repeaternummer` int(11) DEFAULT NULL,
  `StammdatenID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID_Standort_idx` (`StandortID`),
  CONSTRAINT `ID_StandOrt` FOREIGN KEY (`StandortID`) REFERENCES `t_standorte` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;






CREATE TABLE `sensors` (
  `sensorID` bigint(11) NOT NULL,
  `Number` bigint(20) unsigned NOT NULL,
  `ValueIDs` text NOT NULL,
  `MeasureTime` datetime NOT NULL,
  `StartTime` datetime NOT NULL,
  `Configuration` int(10) unsigned NOT NULL,
  `LogCycle` int(11) NOT NULL,
  `Alarms` text NOT NULL,
  `AlarmType` int(11) NOT NULL,
  PRIMARY KEY (`GesySenseID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

UNION query:

CREATE TABLE sensors_1
  SELECT * FROM `sensors`
    UNION
  SELECT * FROM `t_modul`;

Best Answer

have you tried this?

CREATE TABLE t_cross_result AS
SELECT *
FROM 't_modul' CROSS JOIN 'sensors'