I try to insert millions of records into a table that has more than 20 indexes. In the last run it took more than 4 hours per 100.000 rows, and the query was cancelled after 3½ days... Do you have any suggestions about how to speed this up. (I suspect the many indexes to be the cause. If you also think so, how can I automatically drop...
Given an xml document that looks like this here: <parentRecords> <parentRecord field1="foo" field2="bar"> <childRecord field1="test" field2="text" /> <childRecord field1="test2" field2="text2" /> </parentRecord> <parentRecord field1="foo2" field2="bar2"> <childRecord field1="test3" field2="text3" /> ...
I'm passing a large dataset into a mysql table via php using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of an mile long string and then executing it. I am using the codeigniter framework so its functions are also available to me. ...
Is it possible to BULK INSERT (SQL Server) a CSV file in which the fields are only OCCASSIONALLY surrounded by quotes? Specifically, quotes only surround those fields that contain a ",". In other words, I have data that looks like this (the first row contain headers): id, company, rep, employees 729216,INGRAM MICRO INC.,"Stuart, Becky"...
I'm trying to copy data from one Oracle schema (CORE_DATA) into another (MY_DATA) using an 'INSERT INTO (...)' SQL statement. What would the SQL statement look like? Thanks, ...
Hi guys Does anyone have any tips that could help speed up a process of breaking down a table and inserting a large number of records into a new table. I'm currently using Access and VBA to convert a table that contains records with a large string (700 + characters) into a new table where each character has its own record (row). I'm do...
Hi I have an issue and I have looked long and hard over the Internet for an answer but cant find anything. I have a little app that sucks in a web service. It then passes this web services results another applications via its own web service and stores the request in a table. What I am trying to do is quickly import the results to ...
How do I bulk insert with SQLite? I looked it up and it seems like I do an insert with a select statement. I googled, looked at the examples and they all look like they are copying data from one table to another or is not compatible with SQLite. I want to do something like "INSERT INTO user_msg_media (recipientId, mediaId, catagory, cu...
Java has the PreparedStatement addBatch + executeBatch to do multiple insertions efficiently. What is the fasted way to do a batch of inserts using php's mysqli extension? Thanks! ...
I want to import a one column text file into one of my sql tables. The file is just a list of swear words. I've written the following TSQL to do this BULK INSERT SwearWords FROM 'c:\swears.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) However it errors with unexapected end of file. The table im importing to is just an id...
Hello, I can't seem to figure out how this is happening. Here's an example of the file that I'm attempting to bulk insert into SQL server 2005: ***A NICE HEADER HERE*** 0000001234|SSNV|00013893-03JUN09 0000005678|ABCD|00013893-03JUN09 0000009112|0000|00013893-03JUN09 0000009112|0000|00013893-03JUN09 Here's my bulk insert statement: ...
I want to transfer data from a vertical db layout like this: --------------------- | ID | Type | Value | --------------------- | 1 | 10 | 111 | --------------------- | 1 | 14 | 222 | --------------------- | 2 | 10 | 333 | --------------------- | 2 | 25 | 444 | --------------------- to a horizontal one: ---------...
I have a 5GB file that I need to bulk insert into a SQL Server database (2005 SP2). When I attempt to do so (using a format file that worked in the past for the same import file), I get the error: The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in ...
NO MySQL answers please! The basic query is as follows (assume A is Key) INSERT INTO destination (A,B,C) SELECT a1,b1,c1 FROM source WHERE (selectconditions) ; Source contains many records that may or may not already be in destination, which means that the insert will fail as soon as a duplicate record is encountered. Desired Behav...
We're trying to figure out the best way to handle BULK INSERTs using Oracle (10gR2), and I'm finding that it can be a pretty complicated subject. One method that I've found involves using the Append optimizer hint: INSERT /*+ Append*/ INTO some_table (a, b) VALUES (1, 2) My understanding is that this will tell Oracle to ignore indexe...
Hi, I have recently developed a C# application using Linq. I am getting from an external database a list of profiles I need to process, some are new and some are already in the database, and need to be updated. What I do today is go over the profile list and check each profile if such exists I update otherwise I insert - this solution is...
Hello, I'm using in my application a mix Spring/Hibernate (nothing original). For a given feature, I have to import the content of a CSV file into a table of my Oracle DB. For now, I juste create the objects, I do HibernateTemplate.saveOrUpdate on each of them (I need to retrieve their newly allocated Id) Then the transaction occ...
I have a file wich has about 12 millon lines, each line looks like this: 0701648016480002020000002030300000200907242058CRLF What I'm trying to accomplish is adding a row numbers before the data, the numbers should have a fixed length. The idea behind this is able to do a bulk insert of this file into a SQLServer table, and then pe...
i have extracted a list of usernames using the answers from this: http://stackoverflow.com/questions/1273259/how-to-optimize-mysql-query-if-i-have-too-many-or-operators-in-my-query now i have another issue. I have a friend table made up of just 2 columns, requestor and buddy both form a composite primary key. Now suppose i have an arr...
I have a bunch of data that i want to insert and i have decided to use bulk insert for mysql. insert into friends (requestor, buddy) values (value1, value2), (value2, value1), (value3, value4), (value4, value3), ... i would like to know the following: 1) can i use ignore? eg insert ignore into friends (requestor, buddy) values (value...