Bulk Insert Of New Records In Sequence
I am looking to create a querty that searches for the max value a field
called listnum in a table called tbl_listing and then interests 25 new
records with listnum's starting 1 higher then the max value.
ie if the highest listnum is 1000 it should insert new records with a
listnum of 1001-1024. All the other fields in the table can be blank.
Is there a simple (or not so simple) query that I can run to do this?
View Complete Forum Thread with Replies
Related Forum Messages:
Bulk Insert
I tried following: delimiter go; // works fine insert into table values(0); go // does not work insert into table values(0) insert into table values(1); go // does not work insert into table values(0); insert into table values(1); go
View Replies !
How To Do Bulk Delete Of Records?
I have MySQL with Phpmyadmin front-end on a Centos machine. I have a table with with two fields - Member_id & member_name. Both fields are unique. Now, the table has over 100,000 records and I have to manually remove about 5000 members from one table and add them to another similar table. I have the list of members to be deleted - member_name in a text file, But Phpmyadmin allows only searching one-by-one. How do I do a bulk delete of all these 5000 members?
View Replies !
Insert Bulk Data
Hello, first post here, hope i'm not braking too many rules, I have a question so I'll get to it: I have a file with the rows in this format: XXYYYYYYYYYY xx xxxx the XX is a fixed header, the YY is the info I want and the rest is garbage. Can i Specify that i want the data from a specific point to a specific point? and not the whole line?If so, can i specify this when using LOAD DATA INFILE ?
View Replies !
Bulk Insert Error!
Whenever i run a bulk script i get the error "MySql has gone away". When i research i found that this is because by default mysql allows bulk inserts of up to 1MB and can be expanded to 1GB How do i achieve this ie expanding to aroung 512mb?
View Replies !
Why Is This Bulk Insert Not Working?
I'm trying to insert some data from a file into MySQL but instead of multiple rows I get a single row with one and a half rows of data in it. I think it is something to do with it not reading the line breaks properly. Code for insert: load data local infile '/forums.txt' into table Forums fields terminated by '|' optionally enclosed by '"' lines terminated by ' ' (name, description); Text in file: "General Discussion 1"|"All general discussions 1 go here" "General Discussion 2"|"All general discussions 2 go here" "General Discussion 3"|"All general discussions 3 go here" "General Discussion 4"|"All general discussions 4 go here"
View Replies !
Bulk Insert From Text Files
Is there a way to do a bulk insert (more efficient way) from a text file versus 3.5 million insert statements? This is for taking data from a csv file and loading into mysql 5
View Replies !
Bulk Insert Of 1000 Rows
I have made a script so that it does one bulk insert of 1,000 rows at a time. Would using Load Data Infile on the same 1,000 rows be quicker or just the same speed when uploading data to the database? And if so, then how much faster would it rougthly be? I have looked high and low, but could not find anything about this. In MySQL it says that it can be up to 20 times faster, but that is for single insert for each row. I am doing just one large insert and not individual inserts per row.
View Replies !
How Do I Bulk Insert The Images/pictures
I use Deltascripts PHP Classifieds (v6.12) We have a customer that wants to upload 800 ads to our database. I believe they're going to deliver the ads in excel/csv format. That's ok. But what about the ads images? How do I bulk insert the images/pictures and associate each picture to the pre-bulk-inserted ads? Table Picture structure: id int(4) del int(11) pictures_siteid bin_data longblob filename varchar(50) filesize varchar(50) filetype varchar(50) imagew varchar(10) imageh varchar(10)
View Replies !
Old Records Re-appear On Insert
insert into tablname (col1,col2,...) values ('value1','value2',...); I am developping, so sometimes I delete all the contents of the table and start all over again. What happens is this: when I insert a value in col1 that was deleted before from the table it inserts the new value AND the old value. One of the columns is a datetime that tells me the exact insert date (in the past!!). The new record has the correct insertion datetime. So now I have two records (sometimes even more, if I used the value more often before), the old one and the new one! What's going on?
View Replies !
Insert Records
I have 2 tables namely Professors and Subjects, i want to insert data of professors to Professors table and data of subjects related to particular professor to Subjects table. How do i insert values into multiple or related tables?
View Replies !
MySql Insert Between Two Records
How can i Insert a row in between. e.g. +----+------------+ | Id | Name | +----+------------+ | 1 | James Bond | | 2 | John Doe | +----+------------+ I want to inset a row in between the two entry. but it will be in a larger scale database, like rows in millions.
View Replies !
Insert Or Update Records
What's the best way to achieve this? To simplify the problem, lets say I have a table with two columns, 'id' and 'value'. Now, I want to pass an object through remoting (amfphp, irrelevant really but anyway) to either update existing rows or insert new ones. The object I'm passing has corresponding fields, and if there is a match on the 'id' field then update it, if not insert a new row. So I guess I need to first pull out all of the id's and compare them to the ones in my object, then seperate them up to ones that need updating, and ones that need inserting. I can't quite see what the best way of doing this is. Either in code e.g. PHP or is there a MySQL statement to do it the quick way?
View Replies !
How To Insert Same Value To A Series Of Records
i have a table 'sip' (id,keyword,data,flags). i have a few records which need to have the id as 0 (meaning they r common for all users who register). i can enter these manually into the dB. While entering the details of the users who register, i shd have a particular 'id' value begining with '1', for each user. (since 0 has already been taken by the previous common records). The problem is: each user has a set of 3 records to be inserted. So, the 'id' needs to be set to '1' for 3 records (every user). then to '2' for the next 3 records and so on..
View Replies !
Insert Copied Records Using MS Access
I've been using MS Access as a front end to our remote MySQL db for a long time. I've just added a facility for clients to select a record to copy along with all its sub-records. The initial record copies accross ok, but I seem to be having a spot of bother getting the sql syntax to work. I keep getting an ODBC error 3146 ? Code:
View Replies !
MySQL Insert Records Using MS Access
I have a MySQL database linked to an MS Access front-end. I have an app which works fine with the local copy of the Access DB, but when run with the linked tables for MySQL, it fails. I'm trying to copy over related data for products, which is held in seperate tables. The main copying of the producy works fine, just not any related product data. Code:
View Replies !
Multiple Insert, Unique Records Only
I want to insert lets say 100 records into a db that looks like the following: ID auto increment IPAddress varchar The query I am running inserts all 100 with one trip to the DB using php. What happens If I only want Now lets say I do not want more than 1 IP in this database. Surely I don't have to search the whole database looking to see if there is a match for IPAddress.
View Replies !
Most Effecient Way To Insert Multiple Records
i have some areas on my site where i'm inserting hundreds of records. currently i'm looping through each record and performing the insert. something like... PHP Code: foreach ($records as $record) {Â Â Â Â Â // insert into mysql} is there a more efficient way of doing this? can i not send all of the records at once to mysql? like a transaction-type thing?
View Replies !
Insert Records To Remote Mysql Server
for a insert statement, there are two packets on the network between the client and the server. so, the packet's number will be double of the number of insert statement. that is rough. I want there will be a few packet used for many many insert statement. Is there any way to make that true.
View Replies !
Insert Table Records Without Typing Query?
I'm a MySQL newbie but no stranger to Microsoft SQL Server. I'm primarily used to using Microsoft SQL Server via SQL Enterprise Studio, so I'm liking the MySQL Administrator GUI as it has a familiar feel to it. One question (so far): in SQL Enterprise Studio, I could open a database table and insert a record simply by typing in the GUI grid (or by pasting a record from my clipboard). In MySQL Administrator, I don't see a way to do that. Is it not even possible? If not, I'll stop looking - but if there is a way to do so, I'd love to know. For example, I'd like to be able to copy a record from an Access database or even an Excel file, and paste it into a MySQL table - not have to write an insert query and execute it.
View Replies !
Lock Tables To Update And Delete, But Insert New Records
I'm trying to "lock" mysql tables - to prevent rows being updated/deleted - I still want to be able to insert new rows (legal reasons). I have toyed with the idea of changing the user permissions - can I simply change it so that no-one can perform an update on that table, even root? But I feel there must be a simpler way that I and my many web searches am missing.
View Replies !
How To Insert New Records Based On Previous Record Values?
I have TABLE: year value It has for example, year, code, vlaue 2007, 33, 4883 2006, 33, 4883 2007, 32, 4883 2006, 32, 4883 2008, 31, 4883 2007, 31, 4883 2006, 31, 4883 I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%. My several attempts have led to this but which does not work. UPDATE TABLE a SET a.value = b.value * 1.05 WHERE b.year = 2007 AND code NOT IN (SELECT code FROM TABLE b WHERE year =2008 AND value !=0)
View Replies !
Is There A Simple (!) Way To Auto-generate And Insert 10000 Sample Records Into A Table?
I want to test the response times and performance of MySQL DB with a large table. For this I need a way to insert thousands of automatically generated records into a previously created table. Is there an easy way to achieve this? Is there a script feature which let me do something similar like For i=1 to 100000 DO ( INSERT INTO TAB234 VALUES (i, randomNum(1,10), randomChar('a','z', length=12), 'ALWAYSTHISTEXT') )
View Replies !
Bulk Load
i am using the perl DBI. -- how can i make load_file read a blob. it treats the file contents as a string rather than text/blob. -- is there an ENV or mysql var to tell the engine where to find files you want to load? mysql looks in ~ by default and i know i can use a full path but it is reather long and cumbersome.
View Replies !
Bulk Rename - Best Practices?
Was trying to modify all tables in a particular database starting with the same prefix, i.e., "tbl_". Decided I'd rather have them all start with "_tbl_" instead. Being lazy, didn't want to rename them one at a time. Finally: 1) Went into phpMyAdmin's SQL window. 2) Ran "show tables like 'tbl_%';" 3) Copied the resulting column into my text editor. 4) Fiddled with regex search / replace & a tiny macro to create "rename table tbl_mytable to _tbl_mytable" and so on, one table per line. 5) Ran that file as source in a CLI session. So, they're renamed. But am just curious if there was a more direct way, rather than having to suck all the names in & massage a source file. AFAIK, RENAME does not support wildcards, which is where I got stuck. So, the question is: Was there a way to have done this entirely within MySQL (i.e., with one statement), as opposed to the method I used?
View Replies !
Bulk Data File
I have a data file and I need to retrieve just two values from the file. The positions of those values however remain the same in each time. Is it better to load the complete file onto the database and then retreive the two values out of it or is it better to load just those two values on to the table.
View Replies !
Bulk Loading Data
I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures. Right now I can load into a MySQL database via ODBC, but I need to improve throughput. How can I bulk insert the data more efficiently? I would prefer not to use LOAD FILE because it involves converting the binary format to text, which will have a much larger footprint. To illustrate the pain of text files in my application, a single binary file might contain up to a million rows! And I might load hundreds, if not thousands of these files in a day! How can I load data directly into the database at a lower more direct level than LOAD FILE? What are my options to integrate my data parser with MySQL?
View Replies !
Bulk Load A Zip File?
is it possible to do this? with the: LOAD DATA LOCAL INFILE command, is there anyway to specify a zipped file? I did some searching and couldn't find any info, so i assume there isn't, but i wanted to run it past you experts first!
View Replies !
Bulk Loading Date
I would like to load data file into a table called Items. Here's the description of Items(ItemId, Name, ExpiredDate) I am having problem filling up the ExpiredDate field. The type of ExpiredDate is DATETIME. Here's my load file LOAD DATA LOCAL INFILE "Items.dat" IGNORE INTO TABLE Item FIELDS TERMINATED BY '<>' (ItemId, Name, ExpiredDate) quit
View Replies !
Bulk Load Of Geometry Type?
I am trying to use the geometry data types introduced in version 4.1-alpha and the spatial indexing seems to work well. But I have a large volume of data to load, and from the documentation all I can see is that I have to load using a set of INSERT (or UPDATE) commands, which are rather verbose, and which produce a lot of messages each time a row is inserted: INSERT INTO tm VALUES ( 0.01082, 0.08536, 0.46,16.84,16.129,15.856,polygonfromtext('polygon( (0.00 999 0.08452, 0.01165 0.08452, 0.01165 0.08619, 0.00999 0.08619, 0.00999 0.08452))')); I have made the inserts load many rows at a time, which helps a little, but it is still slow. Does anyone know if there is a bulk load format, suitable for use with LOAD DATA INFILE...
View Replies !
Bulk Delete WP Post Via Mysql Query
I want to delete posts that I have made in a particular category in WP [more than 2000+] How can I delete all the post from a particular cateogy[Uncategorized] via a MYSql query. What query do I need to run?
View Replies !
Disrupted Sequence
Another brainteaser: CREATE TABLE IF NOT EXISTS `test` ( `id` INT(11) unsigned NOT NULL auto_increment, `cid` VARCHAR(10) NOT NULL, `item` VARCHAR(10) NOT NULL, `val` INT(11) NOT NULL, `dt` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 In the above example table I need to find the first 5 highest values of val that break the sequential set of values. In other words: say I have values 49,48,45,44,43,40,39,37,36,34 of val in my table, with 49 being the highest value of val (need to sorted first, because values may randomly be dispersed in my table), then I want to get 47,46,42,41,38 as the query result.
View Replies !
Create A Sequence?
I am creating an application that will contain three tables that need to share the same sequence. Upon digging through the docs, I do not see how I could do this. Could someone give me some guidance into this? Also, As I read the docs, when I am creating tables, there is something about an InnoDB? Sorry to be confused, but I am not sure how to begin creating my database, nor tables without this knowledge.
View Replies !
Sequence Substitute
I have a problem about create sequence number which can be reset after MAXVALUE has been assigned. In the older version of mysql i can use "create sequence", but in the recent version i can't do that anymore. All i know in mysql ver 5 is "Auto_increment" which cannot be reset number without delete or truncate table.
View Replies !
CREATE SEQUENCE ?
Tracing some examples in a book from Apress, for some reason they go for Oracle in this book - not my cup of tea, especially not when I am sitting on a vista-terminal. Enough of my complaing. How do I create a sequence in mysql ? This is the example I am stuck with: CREATE SEQUENCE SEQ_ID_GEN INCREMENT BY 1 START WITH 100 MINVALUE 1 CACHE 50 ; How do I write that sentence in mysql ?
View Replies !
Sequence Generation
I was wondering if there was something close to CREATE SEQUENCE in MaxDB for standard MySQL. I'm thinking of just doing a table/stored procedure and calling it a day, but was wondering if MySQL had some other alternative.
View Replies !
What Collation Sequence?
I've just transferred a small PHP/MySQL application from a Firepages phpdev5 install to an XAMPP install, so I've got new versions of everything. I used phpMyAdmin to export and import the tables of the database. When recreating the database on my new install I selected the default collation (latin1_general_ci) but my application is now displaying ? in place of a number of characters so I guess I got it wrong. I can't see where in the old phpMyAdmin (2.3.0-rc3) to determine what collation the database uses.
View Replies !
Optimising Bulk Inserts On Large Indexed Table
I'm trying to optimise large imports into a large table with keys. Here is the story so far. Can anyone suggest ways of creating a scalable solution? >> I want to allow periodic real time imports of up to 100,000 rows into a table with more than 2,000,000 rows. The table has 2 text indexes. I use a 'load data infile' which is quick. Or was, until the number of rows in the table grew and the inserts slowed down because of index rebuilding (I assume). I then started disabling and enabling the keys before and after the import, and ran the enable part off as a background process so the user didn't have to wait for it. However, 'enable keys' locks the table, which makes selects and other operations on that table impossible during the process (currently about 2 mins). My system variables look like this: [mysqld] set-variable = max_connections=500 safe-show-database ft_stopword_file='' ft_min_word_len=3 default-character-set=utf8 init-connect='SET NAMES utf8' query_cache_type=1 query_cache_limit=2M query_cache_size=64M bulk_insert_buffer_size=64M key_buffer_size=128M log-slow-queries delay-key-write=ON
View Replies !
Bulk Renaming Desktop Files Using Names From MySQL?
I had thousands of JPG images descriptively named 'White Cat.jpg' 'Old Horse.jpg' etc. and I accidentally renamed all of them as numbers. 1100.jpg 1101.jpg etc I do, however, have all these original file descriptions in a mysql database along with the current filenames of the images in the same table. Does anyone know how I can use MySQL to perform a bulk rename of my JPG images, pulling the filename data from the database to rename these JPGs on my desktop?
View Replies !
Identity, Sequence, Serial
Does MySQL offer capability for any of the following? - IDENTITY - SEQUENCE - SERIAL Or is AUTO_INCREMENT the catch-all for the functionality of all of the above? I ask because I'm looking into Java EJB3 which describes support for any of the above (as well as AUTO_INCREMENT, so I'm not out of luck completely).
View Replies !
Re-arrange Primary Key Sequence
i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g 1 - entry 2 - entry (3 - deleted no mere here) 4 - entry 5 - entry When key is not there my AJAX application fails to run properly due to not finding next id. Is there any method to re-arrange primary key values to a proper sequence when one or more numbers are missing?so that i will write a query and whenever user deletes an item i will re-arrange the primary key sequence automatically
View Replies !
Selcting Date Sequence
I am trying to return a set or rows that represent all of the days between two dates. For example, I want to select all the days between December 25th, 2005 and January 5th of 2006, and i am looking for it to return (shown here in CSV) 2005,12,25 2005,12,26 2005,12,27 2005,12,28 2005,12,29 2005,12,30 2005,12,31 2006,01,01 2006,01,02 2006,01,03 2006,01,04 2006,01,05 So, for any date, i want to select all the days in between. Any ideas?
View Replies !
|
TRACKER
