Inserting Large Amounts Of Data Into Mysql
I have a whole bunch of numbers and state, county, city, etc. data that i'm trying to load into a database for a demographics website. The data is all in excel currently so I could easily copy and paste it to a text file or another medium if necessary but I'm wondering what the quickest and most efficient way to get it all entered into mysql is.
View Complete Forum Thread with Replies
Related Forum Messages:
Import Large Amounts
My client has a large database in MS Access. I need to put a section of this (approximately 900 rows) into MySQL to be displayed on a website. The thing is, every month I will be resupplied with updated data, which will need to replace the data that is currently in the SQL database. I am operating on a Mac and they've given it to me as an EXCEL spreadsheet, which I am trying to import into MYSQL using phpMyADmin with no luck. I've tried exporting from EXCEL to various formats, such as CSV, but I can't seem to get it to import into the MySQL database Does anyone know what I can do? I need it to be relatively simple so that every month I can simply reimport the new data. Is there some trick to importing the data from EXCEL, or should I ask the client to supply the data from MS Access in a different format.
View Replies !
Handling Large Amounts Of Text
I am desiging a web store to hold and manage up to 150,000 to 200,000 items and have run into a problem which I believe can be fixed but I am not sure about the way to go about it.The problem is that every product has a rich text description that contains a lot of extra HTML code that goes into the database with the product description. I would estimate every product has an average of 10k of description and code. Now instead of putting all of this into the database would it be possible to write this to a .txt file and keep it in a products_desc folder? Would this keep the database at a respectible size and optmize everything?Does anyone have any good examples or tutorials on going about this with PHP?
View Replies !
Inserting VB Data Into MySQL Using Ado
I am trying to insert some data into a MySQL db, with Visual Basic by using ADO. I can select data without a problem using adorecordset.open, to insert would I use adorecordset.addnew? if anybody has done this and has a sample script I would be very happy to check it out.
View Replies !
Inserting Data Into Mysql Database
i need to insert data from a txt file that is updating continously, select those info that i need by line, and after that adding them, i don't think that can be done in mysql; i've foud another program that i've made to read the file and select them, but now i don't know how to insert them in a data base, except php is there another program that can insert data into a mysql database?
View Replies !
Php Mysql Not Inserting Data, No Rerror Msg.
On development machine is a winXP and server for test is linux RHEL. I have a code that gets data from a form and insert into a table. -- $query_po_h = "INSERT INTO po_header (ponum, whs, extref, status, supplier, blkord, podate, remrk1, remrk2, remrk3) VALUES ('$ponum_t', '$whs', '$extref', 10, '$supplier', '$blkord', now(), '$remrk1', '$remrk2', '$remrk3') "; $rs_po_h = $db->Execute($query_po_h) or die("Error in query: $query_po_h. " . $db->ErrorMsg()); -- The problem here is it works on the development machine but doesn't work on the server. It is not generating error message at all. And only this particular query is not working. Other queries with the mysql database be it insert, update, delete or select works fine. Any idea why php-mysql will not work and still don't generate any error message?
View Replies !
Inserting Time Data Into MYSQL
I'm trying to insert an assortment of data into a mysql database. Although I'm having problems when inserting fields with time information. Firstly, it's outputting it as HH:MM:SS and I need it to output as HH:MM. It's also defaulting as "00:00:00" even if I leave it blank. I've set it up as TIME and set the default as blank and NULL ...
View Replies !
Storing A Large Data Struct/object In MYSQL
I have the following structure. typedef __nogc struct RemoteDisplayInfoType{ int HistData [512][512]; int channelNdx; double xVar[2]; double yVar[2]; double paAngle[2]; }RemoteDisplayInfoType; I want to store this in MySQl database and retreive it back. I am using ADO.Net with managed c++.
View Replies !
Writing Large Chunks Of Binary Data To MySQL With ODBC
I'm having a lot of trouble writing large chunks of binary data (tests are in the range of 16-512K, but we need support for large longblobs) to MySQL using ODBC. Database is local on a W2K system, but I have to support all modern Windows systems, and a variety of ODBC configurations. (I'll be testing against multiple ODBC databases soon - but development is against MySQL) I've been able to adapt some example code that executes a statement (SQLExecute) and uses SQLBindParameter to mark the longblob field as SQL_DATA_AT_EXEC. That works. However I really need to use a result set (opened for update), and to add new rows using SQLBulkOperation( hStmt, SQL_ADD) This works for the normally bound fields, but fails if I bind a variable using something like: Code:
View Replies !
Inserting New Data
I have a form that has name, address, city, state, zip. All this goes into a single table. I want to add an additional table to put the cities into. I want to pull the cities from the city tables into a dynamic dropdown to choose the cities from.My problem is if I have a city already in the city table, I dont want to add it again and have duplicate citys in the dropdown. Anyone have a clues on how I would do this?
View Replies !
Inserting Data
Everytime I insert data into a table, The rows are rearranged so the primary key column is in alphabetical order. How does this happen? and how do I fix it?
View Replies !
Mammoth Amounts Of Self Joins
I have a table that contains keywords (Varchars) each one mapped to a product. so the database schema is just an id column, product column and keyword column. I want to select the products that contain x,y,z keywords. Now if this query involves many keywords I end up with a massive amount of self joins on the keywords table, is there a better way to do this than self joins? What would work perhaps is multiple unions where I could discard non duplicated rows but you can't seem to do that. Any ideas... ?
View Replies !
Adding Amounts Of Time
I am looking for a way, as the title suggests, to add a length of time to a time-type field.I needed to type something, and this is what I logically typed (of course, incorrect): SET playtime=playtime + '$length' Let's say playtime = 00:10:30, and $length = 00:04:30. What do I need to change the above statement to, so that the new value of playtime = 00:15:00 ?
View Replies !
Getting The Sum Of Amounts For Each User - How To Join?
I have a simple table for transactions that will store information like a bank account. To find the balance for a given user I want to sum all the amounts and display the result How can I do this for more than one user at a time? CREATE TABLE `transactions` ( `id` int(5) NOT NULL auto_increment, `user_id` int(10) NOT NULL default Ɔ', `created` datetime default NULL, `modified` datetime default NULL, `description` text character set latin1 collate latin1_general_cs NOT NULL, `amount` decimal(10,0) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I'm hoping to get output like: user_id | balance(sum of amounts) ------------------------------------------ 1 | 70 2 | -70 3 | 10 4 | 5
View Replies !
Large Data Sets
I'd like to store a large data set of the following form in MySQL: about 8000 replies (by unique institutions) to a questionnaire with 3000 fields at 120 different dates. Fields are either versions of int or char with lengths between 8 and 75. I need to do searches across all those dimensions (institutions, fields, dates). I'm wondering what the best database design (in terms of query speed) would be (3Ghz Pentium, 1GB RAM, Linux 2.6, one hard disk).
View Replies !
Insert A Large Of Data
how to insert a large of data in .sql file format (include CREATE TABLE and INSERT TABLE), because after I export data to backup from my website, data file aprox 50MB, and script always timeout when I restore data using phpMyadmin? My Server use Linux OS and I have root account. May I use command line? Please help me and guide me step by step
View Replies !
Inserting Data Into Two Tables
if I want to insert data into two tables how do I do this? Is this right? I'm guessing not as it doesn't work What should I do? $query = "insert into requests (word,status) values ("$req","pending") AND insert into known_by (word,user_name) values ("$req","$user_name")";
View Replies !
Problem In Inserting Data
I have a table with 8 fields ,,in which one PK and two indices ,, now I want to insert a new data , and to avoid dublication I want to check the two indices together IF they are equal to the similar fields in the coming data , I want to remove the existed or update it completely (changing all the fields) , so I tried using INSERT.....ON DUBLICATE UPDATE .. it will check only the PK or one index then give you the chance to update one field ..(that what I understood about it). another way is , using REPLACE , but also it only checks the PK ... I dont want to check the PK.. it is only (AI) auto increment number HOw to do it saying like this : INSERT INTO mytable (A,B,C,D) VALUES (A1,B1,C1,D1) and IF the stored A == the coming A AND the stored C==the coming C THEN delete the existed or dont insert this row
View Replies !
Inserting Data Into Table
My task is to create a table which works as a "linker" of two other tables. In this linker table as foreign keys I am using keys from this two tables. I am inserting data into first table t1 in one loop in Perl script, then in a second loop I am inserting data into table t2 and immediately after into t3 which is a linker table between t1 and t2. I have no problems with id (primary key) from t2 , but while extracting keys from t1 there is message: Subquery returns more than 1 row after subquery: insert into t3 (t1id,t2id) values ((select id from t1 where name like 'x'),(select max(t2id) from t2)); simply because value '' are present in table t1 two times so it has two keys. How this problems can be solved? I tried to use: insert into t3 (t1id,t2id) values ((select id from t1 where name like 'x'=ANY(select id from t1 where name like 'x')),(select max(t2id) from t2)); however then there was a message:ERROR 1048 (23000): Column 'id' cannot be null because it is t1 primary key? Are there any ways of dealing with this problem?
View Replies !
Inserting Dynamic Data?
In the connectorJ example: stmt.executeUpdate( "INSERT INTO autoIncTutorial (dataField) " + "values ('Can I Get the Auto Increment Field?')", Statement.RETURN_GENERATED_KEYS); it works perfectly fine for static text. I can't seem to figure out a way to get it to accept dynamic data (from java beans etc) i.e. mybean.getA(), myBean.getDate for example: "values ('Can I Get the Auto Increment Field?'" + getA() ) gives me a compilation error. What's the proper syntax?
View Replies !
Inserting Data Into A Table
insert into enrolls (section_id,total) values(324,max(sub) +.1); how can I make it work so the value for the total of this entry is the max of the sub column from the same table + .1?
View Replies !
Trouble With Id + 1 When Inserting Data
I'm trying to find a good alternative for auto_increment, the reason is that I need to know what the next value of id will be before inserting data into the table and I haven't found a relyable way of doing that with auto_increment. My plan was to use a commend like: INSERT INTO temp (id, test) VALUES (MAX(id)+1, 'foo') But I am getting the error: ERROR 1111 (HY000): Invalid use of group function.
View Replies !
Problem Inserting Data
I'm having an issue inserting data into a MySQL database using PHP code which has worked before. Some of the fields in the form have been removed and so are not passing data across to the insert script and the insert has been altered to show this, apart from that everything is the same. Could anyone help me diagnose what is going on this code to not make it work, I've been looking for the last 2 hours and just cannot find the problem. Data entry page PHP <? if(isset($_GET[submit])){ $error='' if ($_GET['heightcm']=='') { $height=($_GET['heightinches']*2.54); } else { $height=$_GET['heightcm']; } if ($_GET['widthcm']=='') { $width=($_GET['widthinches']*2.54); } else { $width=$_GET['widthcm']; } if ($_GET['depthcm']=='') { $depth=($_GET['depthinches']*2.54); } else { $d
View Replies !
Very Large Data-files - Performance?
I have to handle a large amount of Data (about 5GB) in one table. I want to extract the essence out of this table which should be something like 1MB and save it to a new table. Mostly used commands are GROUP, WHERE, BETWEEN, IN and LEFT JOIN. I use 3-4 temporary Tables. The script is ready and tested on a file of 60MB (750.000 datasets) and works fine (needs about 90seconds). What I want to know is: Can I expect the query to work through my big data within some ours, or will I fail with a standard PC? What can I do to optimize (Hardware/Code)? There is only one query to work through at one time.
View Replies !
Managing A Large Piece Of Data
I have a table set up for all of my pages of content. There are columns that store certain small, various information about the page. Then, there is the main content column that holds the page content (actual article). One of my staff members that writes content for my site is complaining that the content gets cut off. I imagine it's because it's bigger than 65,000 bytes, which is all that the "TEXT" type of field will allow (that's what type of field my main content is... TEXT) My question is what type of field should I change my main content column to an order to fit a larger piece of data? I've never had to this before, so I don't really know anything other than TEXT. Sixty-five thousand bytes isn't cutting it though.
View Replies !
Large Insert On Customized Data
We are doing some scripting to send out bulk email from a database. The database has a table containing user email addresses along with other user information. We want to select from the user table a list of users, and then insert these into the _mail_queue table so we can do batch processing of the emails. The problem is lets say we need to send to 10,000 users. We would have to do 10,000 insert queries? I understand we could do a nested select, but some of the data entered into mail_queue is going to be customized first. (Example: Dear User's First Name,)
View Replies !
Inserting Unicode Data In Forms6i
i m using forms 6i with oracle 10g. i have set Nls_lang =American_America.UTF8. Now my problem is when i type some data i marathi Using a font converter engine directly into the text box on form i just get ?in the text box. when i type same data on notepad n then paste it in textbox it gets properly pasted and also get inserted in oracle.I m also able to retrive it back properly.So i think it is not the problem of my character set.Also i have set the font of textbox as Arial Unicode Ms. i m not getting where the problem is?
View Replies !
Inserting Data With A Partial List.
If I have an auto increment field in a table and I were to insert data into other rows that were in the table but left the auto incremented one out of the list of columns to insert would it cause an error?
View Replies !
Inserting Data Into Record With Condition?
i would like to insert data with "where" condition is this correct codes below? $query = "INSERT INTO photos (comments) VALUES ('$comment_tmp') where ID = '91'; I want to insert the $comment_tmp into comments only on ID = 91. Is this correct or wrong?
View Replies !
Inserting New Data Within A Structured Sequence
I'm trying to organize rows in a specific sequence. However, I also want the capability to insert new rows within that sequence and not simply append them to the end of the dataset as autosequencing would have you do. How is this accomplished?
View Replies !
Inserting Data Into Multiple Tables
I am just learning both MySQL and PHP, and have a question about a project I am working on. The first (and main) table for this project has the basic information about an refund request, including an autoincrement unique ID. Another table includes the products and uses the ID as a reference. The third table is the history of everything that happens to the request. For example, the information may be as follows. Credit table ID - autoincrement status - requested, needs authorization, processed, etc agent - who entered the request customer - person requesting the refund other customer info order table ID - reference to Credit table Item - item ID for amount requested quantity - number purchased price - price of each item (total information is calculated on above fields) history table ID - reference to credit table seq - id for each history log old status - the previous status new status- the status after the update to the record agent - agent making the change notes - a description of what was done. I have the PHP form that collects the data to be entered. My question is, since the first table is an autoincrement, what is the best way to insert a record into the Credit table, pull the ID that was just entered into the credit table and insert that ID into the order and history tables?
View Replies !
Inserting Huge Amount Of Data In DB
I've got a script that scan .csv files and have to add data in a DB. I've try to save data into array and only insert data at the end in one shot, but I got memory problem. I would like to know the best way to add about 1 million of data in DB. Do I go with one query per data? Do I try to say cumulate like 100 data and then insert them and repeat this until it is done?
View Replies !
Importing Large Amount Of Data Does Not Work
I am importing large amounts of data (over 50 Meg) into a new mysql db that I set up. I use[color=blue] >mysql dbname < importfile.txt[/color] But I keep getting timeouts and errors due to the data being too large. I know that since if I break the imported data into multiple chuncks (by importing a few tables at a time) then everything works.
View Replies !
Importing Data Containing Large Text Fields
I'm in the middle of importing a very large database in MDB format into MySQL. The data consists of a fairly disgusting list of redundant columns, and one of those columns contains plain text, including line feeds and carriage returns. I've been bashing my brains out trying to import this data into MySQL WITHOUT resorting to using a BLOB (binary or otherwise) field to stick all that text data into. Varchar fields are fine, but there's a problem importing that data. The problem is that since the textual data in that field contains linefeeds and carriage returns, exporting it as quote-delimited text is fine, except that there's no way for MySQL's LOAD DATA statement to see the linefeeds in that text as anything except newlines - and since most data export tools I have only provide simple CR/LF (linefeeds and carriage returns) as row delimiters, MySQL kept complaining about missing columns when it got to a field containing that raw text. The fix was to append a text column to the raw table, containing an identical string in every row - I used XYZ, but almost anything goes. Then I re-exported the whole table using " string delimiters and CR/LF line terminators as usual. Code:
View Replies !
How Speed Up Data Retrieve From Large Table
I try to retrieve duplicate data from database but it take a lot of time to display and very slow.Even using index also same .Here is my sql statement : $sqlselect2 = "SELECT DISTINCT p1.resume_id, p1.user_id, p1.email, p1.name, p1.gender, p1.house_tel, p1.office_tel, p1.hand_tel, p1.new_ic, p1.old_ic, p1.address1, p1.address2, p1.city, p1.dob FROM resume_personal p1 ,resume_personal p2 where p1.$searchString=p2.$searchString And p1.resume_id!=p2.resume_id Order by p1.$sort $order "; $result2 = mysql_query($sqlselect2,$conn) or die(mysql_error()); Anyones got the better solution to retrieve duplicate data or to improve the speed?
View Replies !
UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it. update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id = b.id and a.col2 = b.col2; The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back. I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states. How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved. Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)
View Replies !
Large Data, Should It Be Split Into A Separate Table?
I'm designing a database and need some opinions. I'm doing a table for blogs, and in it are things such as: - author ID - post ID - date - votes - icon - content Now, the content is huge since it contains lots of text. Someone suggested that I split off the content into a separate table to save space and increase speed for listing for blogs and processing. How true is this? I'm thinking it's rather unnecessary, unless the speed gains are true.
View Replies !
Inserting 100 Rows Of Data Into An Existing Table
I want to import 100 rows of data from an Excel spreadsheet into an existing MySQL table. Both the spreadsheet table, and the MySQL table have the same exact format & headers. In MySQl Query Browser, I can locate only the Edit function, which seems to only allow me to type in the data line by line. Can I import the data in MySQL Query Browser, or do I need another product? And if I can import, how do I do this?
View Replies !
Inserting Data Into A Table From A Text File
I have a table in MySQL with the columns: "ID, JokeText, JokeCategory, JokeDate". I want to take a text file that is a long list of jokes, and load each joke into a new row of the table in the JokeText field. I have tried a bunch of different approaches, and just can't get it to work right. Can anyone enlighten me? If it helps, here is a sample from the jokes text file (yes, they are stupid :P) Q: What goes up and down but does not move? A: Stairs %% Q: Where should a 500 pound alien go? A: On a diet %% Q: What did one toilet say to the other? A: You look a bit flushed. %% Q: Why did the picture go to jail? A: Because it was framed. %% Q: What did one wall say to the other wall? A: I'll meet you at the corner. %%
View Replies !
Table Breaks After Inserting Data With MyISAM
One of my tables keeps getting broken after I add a bunch of data to it. I have reinstalled MySql and recreated the DB several times. In mysql 5 it says the table was marked broken and in mysql 4.1 it says "Got error 127 from storage engine". After I use repair table almost all of the data is gone, and only 10 from 1104 added rows remain, but it's always the same rows. It's the 10 rows where the value for volume is 0 (well it's 000 in the query). I am using the MyISAM DB-engine on my tables. I can fix this problem by changing the db engine to InnoDb, but I wonder why it doesn't work properly with MyISAM? the DDL code for the table is like this: CREATE TABLE `quotes` ( `ID` int(10) unsigned NOT NULL auto_increment, `security` mediumint(8) unsigned NOT NULL default '0', `open` float unsigned NOT NULL default '0', `high` float unsigned NOT NULL default '0', `low` float unsigned NOT NULL default '0', `close` float unsigned NOT NULL default '0', `volume` int(10) unsigned NOT NULL default '0', `splitfactor` float unsigned NOT NULL default '1', `date` date NOT NULL default '0000-00-00', PRIMARY KEY (`ID`), UNIQUE KEY `unique_security_date` (`security`,`date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1 ; I uploaded the insert query I used here: http://users.skynet.be/ecare/insert_quotes.sql
View Replies !
SQL*plus Inserting New Row With Values = To Existing Rows Data
I am trying to insert a new employee into an empolyee table and get some of the values (namely phone no.) to be the same as what another employee currently has. This new employee is replacing the old employee, but i have to keep the old employee alive in the db as other data is reliant on its existance. I was wondering how do i insert the new employee asking the, VALUE ('etc', 'etc',) line, to assign a value to certain fields equal to the value contained in other employee rows.
View Replies !
Inserting Data From Text File Question
I have a sql table with about 9 fields of data in it.The first 3 are filled and the last 6 are currently blank. I have a tab delimited .txt file containing data for each of the 9 fields and want to import it in phpmyadmin to both overwrite the existing data in the 3 fields and fill out the other 6. However I only want to import the data where one of the existing filled-out fields "TournamentID" equals 10. But I'm unsure how to do this. Will this work?: SELECT * FROM `tbl_finish_pos` WHERE TournamentID=10 then check every row returned and select the Import tab in phymyadmin to insert the .txt file
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
|
TRACKER
