Suche befehl zum kopieren

Chr.Raible · 16. April 2007, 13:09

Huhu Jungs und Mädels,

ich hoffe hier kann mir einer helfen

Ich habe zwei Tabellen! Von der einen müssen die Daten in die andere importiert werden.

Die Tabelle mit den Daten sieht so aus:

CREATE TABLE `lansuite_user` (
`userid` int(6) NOT NULL auto_increment,
`email` varchar(120) character set latin1 NOT NULL default '',
`username` varchar(25) character set latin1 NOT NULL default '',
`password` varchar(60) character set latin1 NOT NULL default '',
`type` tinyint(2) NOT NULL default '0',
`rights` varchar(200) character set latin1 NOT NULL default '',
`name` varchar(50) character set latin1 NOT NULL default '',
`firstname` varchar(50) character set latin1 NOT NULL default '',
`clanid` int(11) NOT NULL default '0',
`logins` int(5) NOT NULL default '0',
`portnumber` int(3) NOT NULL default '0',
`wwclid` varchar(15) character set latin1 NOT NULL default '',
`wwclclanid` varchar(15) character set latin1 NOT NULL default '',
`nglid` varchar(15) character set latin1 NOT NULL default '',
`nglclanid` varchar(15) character set latin1 NOT NULL default '',
`lgzid` varchar(15) NOT NULL default '',
`lgzclanid` varchar(15) NOT NULL default '',
`comment` text character set latin1 NOT NULL,
`sex` tinyint(1) NOT NULL default '0',
`street` varchar(50) character set latin1 NOT NULL default '',
`hnr` varchar(5) character set latin1 NOT NULL default '',
`plz` varchar(5) character set latin1 NOT NULL default '',
`city` varchar(50) character set latin1 NOT NULL default '',
`passnr` varchar(15) character set latin1 NOT NULL default '',
`catering_discount` float NOT NULL default '0',
`changedate` timestamp NULL default NULL,
`fcode` varchar(25) character set latin1 NOT NULL default '',
`birthday` date NOT NULL default '0000-00-00',
`newsletter` tinyint(1) NOT NULL default '0',
`perso` varchar(36) character set latin1 NOT NULL default '',
`group_id` int(10) NOT NULL default '0',
`votes_left` tinyint(32) default NULL,
`switch_back` varchar(25) character set latin1 NOT NULL default '',
`barcode` bigint(20) NOT NULL default '0',
`locked` tinyint(1) NOT NULL default '0',
`picture` varchar(80) character set latin1 NOT NULL default '',
`icq` varchar(12) character set latin1 NOT NULL default '',
`msn` varchar(120) character set latin1 NOT NULL default '',
`skype` varchar(20) character set latin1 NOT NULL default '',
`telefon` varchar(20) character set latin1 NOT NULL default '',
`handy` varchar(20) character set latin1 NOT NULL default '',
PRIMARY KEY (`userid`),
UNIQUE KEY `email` (`email`),
KEY `type` (`type`),
KEY `sex` (`sex`),
KEY `newsletter` (`newsletter`),
FULLTEXT KEY `username` (`username`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `firstname` (`firstname`),
FULLTEXT KEY `wwclid` (`wwclid`),
FULLTEXT KEY `wwclclanid` (`wwclclanid`),
FULLTEXT KEY `nglid` (`nglid`),
FULLTEXT KEY `nglclanid` (`nglclanid`),
FULLTEXT KEY `lgzid` (`lgzid`),
FULLTEXT KEY `lgzclanid` (`lgzclanid`),
FULLTEXT KEY `plz` (`plz`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=314 ;

Ein Testdatensatz so:

INSERT INTO `lansuite_user` VALUES (45, 'Chr.Raible@web.de', '[LR]Rattlesnake', 'e10adc3949ba59abbe56e057f20f883e', 2, '', 'Raible', 'Christoph', 1, 73, 0, '', '', '', '', '14475', '38', '', 1, 'Steigweg', '20', '72184', 'GÃƒÂ¶ttelfingen', '', 0, '2007-01-17 16:03:56', 'PUGSUKZWUZEEHGBRYVCYGJTUM', '1927-01-01', 0, '', 0, NULL, '', 0, 0, '0', '', '', '', '', '');

Jetzt sollen die Daten in diese übertragen werden:

CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`nick` varchar(50) collate latin1_general_ci NOT NULL default '',
`passwort` varchar(50) collate latin1_general_ci NOT NULL default '',
`clan` varchar(50) collate latin1_general_ci NOT NULL default '',
`clantag` varchar(50) collate latin1_general_ci NOT NULL default '',
`vorname` varchar(50) collate latin1_general_ci NOT NULL default '',
`nachname` varchar(50) collate latin1_general_ci NOT NULL default '',
`strasse` varchar(50) collate latin1_general_ci NOT NULL default '',
`plz` varchar(10) collate latin1_general_ci NOT NULL default '',
`wohnort` varchar(50) collate latin1_general_ci NOT NULL default '',
`countrycode` char(2) collate latin1_general_ci NOT NULL default '',
`telefon` varchar(50) collate latin1_general_ci NOT NULL default '',
`handy` varchar(50) collate latin1_general_ci NOT NULL default '',
`fax` varchar(50) collate latin1_general_ci NOT NULL default '',
`geb` date NOT NULL default '1970-01-01',
`email` varchar(50) collate latin1_general_ci NOT NULL default '',
`newsletter` int(1) NOT NULL default '1',
`female` int(1) NOT NULL default '0',
`icquin` varchar(255) collate latin1_general_ci NOT NULL default '',
`skype` varchar(255) collate latin1_general_ci NOT NULL default '',
`jabber` varchar(255) collate latin1_general_ci NOT NULL default '',
`msn` varchar(255) collate latin1_general_ci NOT NULL default '',
`yahoo` varchar(255) collate latin1_general_ci NOT NULL default '',
`aim` varchar(255) collate latin1_general_ci NOT NULL default '',
`googletalk` varchar(255) collate latin1_general_ci NOT NULL default '',
`homepage` varchar(255) collate latin1_general_ci NOT NULL default '',
`motto` text collate latin1_general_ci,
`ver_age` int(11) NOT NULL default '0',
`kommentar` text collate latin1_general_ci,
`joinus` int(11) NOT NULL default '0',
`ver_email` int(11) NOT NULL default '0',
`lastpost` int(11) NOT NULL default '0',
`lastgetpasswd` int(11) NOT NULL default '0',
`style` varchar(50) collate latin1_general_ci NOT NULL default '',
`lastactivity` datetime NOT NULL default '0000-00-00 00:00:00',
`lastupdate` int(11) NOT NULL default '0',
`last_ip` mediumtext collate latin1_general_ci NOT NULL,
`lastvisit_forum` int(11) NOT NULL default '0',
`lastvisit_groupware` int(11) NOT NULL default '0',
`settings` text collate latin1_general_ci NOT NULL,
`avatar` int(1) NOT NULL default '0',
`avatar_name` varchar(255) collate latin1_general_ci NOT NULL default '',
`wwclid` varchar(20) collate latin1_general_ci NOT NULL default '',
`wwclclanid` varchar(20) collate latin1_general_ci NOT NULL default '',
`nglid` varchar(20) collate latin1_general_ci NOT NULL default '',
`nglclanid` varchar(20) collate latin1_general_ci NOT NULL default '',
`lgzid` varchar(20) collate latin1_general_ci NOT NULL,
`lgzclanid` varchar(20) collate latin1_general_ci NOT NULL,
`external_id` varchar(255) collate latin1_general_ci NOT NULL default '',
`external_info` text collate latin1_general_ci NOT NULL,
`stats_postings` int(11) NOT NULL default '0',
`stats_comments` int(11) NOT NULL default '0',
`stats_messages_recv` int(11) NOT NULL default '0',
`stats_messages_sent` int(11) NOT NULL default '0',
`stats_sessions` int(11) NOT NULL default '0',
`stats_hits` int(11) NOT NULL default '0',
`stats_profile_hits` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `external_id` (`external_id`),
KEY `nick` (`nick`),
KEY `email` (`email`),
KEY `ver_email` (`ver_email`),
KEY `newsletter` (`newsletter`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=1;

Es müsste doch ein Befehl geben mit dem ich die Daten einzeln von der Datenbank A zu Datenbank B übertragen kann?!

Ich meine so einen art "Copy" befehel!

Der den Inhalt von lansuite_user.id in user.id kopiert!

Danke schonmal im Voraus!

Mfg
Chr.Raible

Torben Brodt · 16. April 2007, 16:04

Hi,

das Zauberwort zum Kopieren unter SQL heißt SELECT INTO
http://dev.mysql.com/doc/refman/5.1/de/select-into-statement.html

Quellcode

INSERT INTO `tabelle2` SELECT * FROM `tabelle1` ;

Suche befehl zum kopieren

Suche befehl zum kopieren

Quellcode

Teilen

Benutzer online 1

Tags