| Author |
Topic |
|
brykendal
Starting Member
2 Posts |
 Posted - 2008-11-16 : 05:06:02
|
Hi and thanks for forums like this! I am completely new to sql and databases. My experience consists of creating a database and adding a user with the help of mysql in my cpanel. I have never created a table on my own or anything else database wise. I have been successful using the import feature for an "sql dump". I have a website that has an existing database and I have an sql dump file that I want to import into that database.The only problem (so far) is that the dump file has a different structure than the existing database. When I try to import my dump file I end up with a "Column count doesn't match value count at row 1" error. My understanding is that I get this error because the existing database has less "fields" than the database structure calls for in the dump file. The actual corresponding fields in the dump file also happen to be out of order than the existing database so even if the correct amount of fields were there, the information would be out of order (I think, doesn't the order matter of the fields in relation to the data?) The existing database uses "tbl_member" and the sql dump I am trying to import uses "Profiles". My journey into understanding sql began when I realised that I would at least have to change Profiles to tbl_member in order to make this work and then realised that there would be just a little more to it than that! My question is, is there a tool or a way to correctly merge the dump file so it would work with the existing database? I have included an example of the existing database structure and the sql dump file structure so you may gain an understanding of what I am talking about. EXISTING DATABASE :-- ------------------------------------------------------------ Table structure for table `tbl_member`--DROP TABLE IF EXISTS `tbl_member`;CREATE TABLE IF NOT EXISTS `tbl_member` ( `member_id` int(11) NOT NULL auto_increment, `lastactivity_` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `body_type` int(11) NOT NULL default '0', `hair_color` int(11) NOT NULL default '0', `looking_for` int(11) NOT NULL default '0', `want_children` int(11) NOT NULL default '0', `marital_status` int(11) NOT NULL default '0', `have_children` int(11) NOT NULL default '0', `smoke` int(11) NOT NULL default '0', `drugs` int(11) NOT NULL default '0', `drink` int(11) NOT NULL default '0', `religion` int(11) NOT NULL default '0', `interest` varchar(255) NOT NULL default '', `headline` varchar(255) NOT NULL default '', `first_date` text NOT NULL, `profession` varchar(255) NOT NULL default '', `skype` varchar(30) NOT NULL default '', `skype_accept` enum('t','f') NOT NULL default 't', `rate_pic` enum('t','f') NOT NULL default 'f', `picture` varchar(255) NOT NULL default '', `member_name` varchar(50) NOT NULL default '', `user_name` varchar(100) NOT NULL default '', `password` varchar(255) NOT NULL, `member_address1` varchar(100) NOT NULL default '', `member_address2` varchar(100) NOT NULL default '', `member_email` varchar(100) NOT NULL default '', `member_county` char(4) NOT NULL, `member_city` varchar(50) NOT NULL default '', `member_zip` varchar(6) NOT NULL default '', `member_country` char(2) NOT NULL, `member_phone` varchar(15) NOT NULL default '', `member_fax` varchar(15) NOT NULL default '', `member_des` text NOT NULL, `gender` enum('m','f','a','b','c') NOT NULL default 'm', `seeking` enum('m','f','a','b','c') NOT NULL default 'f', `height` tinyint(4) NOT NULL default '0', `ethnicity` tinyint(4) NOT NULL default '0', `dob` date NOT NULL default '0000-00-00', `sun_sign` varchar(15) NOT NULL default '', `mail_gender` enum('m','f','a','b','c','z') NOT NULL default 'b', `mail_age_from` tinyint(4) NOT NULL default '0', `mail_age_to` tinyint(4) NOT NULL default '99', `mail_country` char(2) NOT NULL, `mail_looking_for` tinyint(4) NOT NULL default '0', `mail_drugs` tinyint(4) NOT NULL default '0', `mail_marital_status` tinyint(4) NOT NULL default '0', `login_status` enum('t','f') NOT NULL default 'f', `add_date` datetime NOT NULL default '0000-00-00 00:00:00', `is_active` enum('t','f') NOT NULL default 't', `is_paid` enum('Y','N') NOT NULL default 'N', `start_date` date NOT NULL default '0000-00-00', `end_date` date NOT NULL default '0000-00-00', PRIMARY KEY (`member_id`), KEY `user_name` (`user_name`,`password`), KEY `member_email` (`member_email`), KEY `lastactivity_` (`lastactivity_`), KEY `is_active` (`is_active`), KEY `is_paid` (`is_paid`), KEY `member_county` (`member_county`), KEY `member_country` (`member_country`), FULLTEXT KEY `interest` (`interest`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;---- Dumping data for table `tbl_member`--INSERT INTO `tbl_member` (`member_id`, `lastactivity_`, `body_type`, `hair_color`, `looking_for`, `want_children`, `marital_status`, `have_children`, `smoke`, `drugs`, `drink`, `religion`, `interest`, `headline`, `first_date`, `profession`, `skype`, `skype_accept`, `rate_pic`, `picture`, `member_name`, `user_name`, `password`, `member_address1`, `member_address2`, `member_email`, `member_county`, `member_city`, `member_zip`, `member_country`, `member_phone`, `member_fax`, `member_des`, `gender`, `seeking`, `height`, `ethnicity`, `dob`, `sun_sign`, `mail_gender`, `mail_age_from`, `mail_age_to`, `mail_country`, `mail_looking_for`, `mail_drugs`, `mail_marital_status`, `login_status`, `add_date`, `is_active`, `is_paid`, `start_date`, `end_date`) VALUES(20, '2008-11-16 03:43:33', 2, 3, 1, 3, 1, 3, 1, 1, 2, 15, 'motocross', 'Looking for down to earth girl', 'I\\#039;d like to go out for coffee and see if more than the caffeine perks us up!', 'caregiver', '', 't', 'f', '9bca66ffa53b52a223e0c36b862ca8f81226828591Bryan.JPG', '', 'maryanne', 'ed2b1f468c5f915f3f1cf75d7068baae', '', '', 'subbmmissions@comcastic.net', 'USCA', 'san jose', '95118', 'US', '', '', 'I am wandering without that girl, I can smell her, but when I look she\\#039;s not there.', 'm', 'f', 1, 3, '1969-09-02', 'Virgo', 'b', 0, 99, '', 0, 0, 0, 'f', '2008-11-16 03:43:11', 't', 'N', '2008-11-16', '2008-11-15');-- -------------------------------------------------------- SQL DUMP DATABASE :-- ---------------------------------------------------------- -- Table structure for table `Profiles`-- CREATE TABLE `Profiles` ( `ID` bigint(8) unsigned NOT NULL auto_increment, `NickName` varchar(48) NOT NULL default '', `Password` varchar(48) NOT NULL default '', `RealName` varchar(40) NOT NULL default '', `Country` smallint(5) unsigned NOT NULL default '0', `City` varchar(30) default NULL, `zip` varchar(23) NOT NULL default '', `Children` tinyint(3) unsigned NOT NULL default '0', `WhereChildren` enum('I will tell you later','living with me','not living with me','sometimes living with me') NOT NULL default 'I will tell you later', `Headline` varchar(255) NOT NULL default '', `DescriptionMe` mediumtext NOT NULL, `DescriptionYou` mediumtext NOT NULL, `Sex` enum('female','male','couple','TV','TS','Gay') NOT NULL default 'female', `DateOfBirth` date NOT NULL default '0000-00-00', `Relationship` set('act','fri','mar','rel','rom','cas','tra','pen') NOT NULL default '', `Height` int(11) NOT NULL default '0', `BodyType` int(11) NOT NULL default '0', `Religion` int(11) NOT NULL default '0', `Ethnicity` int(11) NOT NULL default '0', `MaritalStatus` int(11) NOT NULL default '0', `Occupation` varchar(30) default NULL, `Language1` int(11) NOT NULL default '0', `Language2` int(11) NOT NULL default '0', `Language3` int(11) NOT NULL default '0', `WantChildren` enum('I will tell you later','No','Yes','Maybe','No matters') NOT NULL default 'I will tell you later', `MerchantPrice` float unsigned NOT NULL default '3', `Email` varchar(50) NOT NULL default '', `EmailFlag` enum('HTML','Text','Not sure') NOT NULL default 'HTML', `EmailNotify` enum('NotifyMe','NotNotifyMe') NOT NULL default 'NotifyMe', `Phone` varchar(30) default NULL, `HomeAddress` varchar(100) default NULL, `IM` enum('none','icq','yahoo','msn','aol') NOT NULL default 'none', `IcqUIN` varchar(15) NOT NULL default '', `Status` enum('Unconfirmed','Approval','Active','Rejected','Suspended') NOT NULL default 'Unconfirmed', `LastLoggedIn` datetime default NULL, `LastModified` datetime NOT NULL default '0000-00-00 00:00:00', `LastReg` datetime NOT NULL default '0000-00-00 00:00:00', `Priority` smallint(5) unsigned NOT NULL default '1', `HomePage` varchar(100) default NULL, `LookingFor` enum('female','male','couple','all') NOT NULL default 'male', `LookingAge` enum('I will tell you later','18-20','21-25','26-30','31-35','36-40','41-45','46-50','51-55','56-60','61-65','66-70','71-75') NOT NULL default '18-20', `LookingHeight` int(11) NOT NULL default '0', `LookingBodyType` int(11) NOT NULL default '0', `Picture` enum('0','1') NOT NULL default '0', `Pic_0_addon` varchar(32) NOT NULL default '', `Pic_1_addon` varchar(32) NOT NULL default '', `Pic_2_addon` varchar(32) NOT NULL default '', `Pic_3_addon` varchar(32) NOT NULL default '', `Pic_4_addon` varchar(32) NOT NULL default '', `Pic_5_addon` varchar(32) NOT NULL default '', `Pic_6_addon` varchar(32) NOT NULL default '', `Pic_7_addon` varchar(32) NOT NULL default '', `Pic_8_addon` varchar(32) NOT NULL default '', `Pic_9_addon` varchar(32) NOT NULL default '', `Pic_10_addon` varchar(32) NOT NULL default '', `Sound` enum('0','1') NOT NULL default '0', `Sound_addon` tinyint(3) unsigned NOT NULL default '0', `Video` enum('0','1') NOT NULL default '0', `Video_addon` tinyint(3) unsigned NOT NULL default '0', `ExtraAddons` set('0','1') default NULL, `LastNavTime` datetime NOT NULL default '0000-00-00 00:00:00', `LastNavPage` int(4) NOT NULL default '0', `Featured` enum('0','1') NOT NULL default '0', `gm_avff_kiss` char(3) NOT NULL default '', `priv_passwd` varchar(32) NOT NULL default '', `PPNotify` enum('NotifyMe','NotNotifyMe') NOT NULL default 'NotifyMe', `seed` int(11) NOT NULL default '0', `guestbook` enum('Enable','Disable','Suspend','Register only','Friends only') default 'Enable', `ProfileType` enum('single','couple') NOT NULL default 'single', `Sex2` enum('female','male') NOT NULL default 'male', `DateOfBirth2` date NOT NULL default '0000-00-00', PRIMARY KEY (`ID`), UNIQUE KEY `NickName` (`NickName`), KEY `Country` (`Country`), KEY `Sex` (`Sex`), KEY `Status` (`Status`), KEY `LastLoggedIn` (`LastLoggedIn`), KEY `Priority` (`Priority`), KEY `LookingFor` (`LookingFor`), KEY `Picture` (`Picture`), KEY `seed` (`seed`)) TYPE=MyISAM AUTO_INCREMENT=2269 ;-- -- Dumping data for table `Profiles`-- INSERT INTO `Profiles` VALUES (30, 'Dipsilskydiver', 'morgan', 'Ronnald', 205, 'CA', '95118', 0, 'living with me', 'No one dies Virgin...life f***s everyone...', 'i m very down to earth person.. i don''t smoke..but i drink ocassionally I work with virgin atlantic airlines at a reputed post. i m here just to make friends..', '', 'male', '1975-07-07', 'fri', 6, 3, 8, 4, 1, 'Workin'' with Virgin Atlantic A', 0, 0, 0, 'Maybe', 3, 'directx@yahoodle.com', 'HTML', 'NotNotifyMe', '1-408-555-1212', '49000,sarasota avenue, california', 'none', '', 'Active', '2004-07-10 22:05:17', '2004-07-10 22:10:32', '2004-06-19 05:51:40', 1, '', 'female', 'I will tell you later', 0, 0, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 0, '0', 0, NULL, '2004-07-10 22:05:17', 6, '0', '', '', 'NotifyMe', 0, 'Enable', 'single', 'male', '0000-00-00');--------------------------------------------------------------- If someone could please help me with this I would really be grateful! Thank you! BryanP.S. Just for the sake of anonymity, the names, emails, addresses and phone associated in this example are bogus. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-11-16 : 11:29:00
|
Hello Bryan,this is a forum for Microsoft SQL Server, not for MySQL.But in your case I would say:- create the table that is fitting to the insert-statements of your dump- execute the insert-statements to get the data into the created table- use other sql-statements to transfer the data from there into your destination table- for correct syntax of MySQL-statements look in some forums of mysqlhope that helpsWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
brykendal
Starting Member
2 Posts |
Posted - 2008-11-16 : 20:46:52
|
| Hi WebFred, Thank you for your help! I really have no idea what you just told me to do, but I will try! I didn't know there was a difference between MySQL and Microsoft SQL Server, I thought SQL was SQL. I will search for the difference between the two. I appreciate you taking the time to answer. BryanBryan |
|
|
|
|
|
|
Importing an existing database into a new database