A core statement in SQL that does what it says on the tin: inserts data into a table.

learn more… | top users | synonyms

1
vote
2answers
142 views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
0
votes
1answer
223 views

INSERT take too much time

I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections ...
5
votes
1answer
993 views

How can I get status messages during a large re-indexing operation with MySQL 5.5?

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
2
votes
2answers
43 views

Copy from one MySQL table to another MySQL table of same database

I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
1
vote
1answer
846 views

Optimize PostgreSQL for a lot of INSERTS and bytea updates

What we have (software): PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf) Windows 7 64 bit Hardware: Intel Core i7-3770 3.9 Ghz 32 Gb RAM WDC WD10EZRX-00L4HBAta Drive ...
0
votes
0answers
13 views

Disable procedures for data load in MySql using Innodb engine

I have a database with an integrated ledger for a multi-unit company. As we bring new units into the database we load their existing ledger data into ours. Unfortunately the existing procedures on the ...
0
votes
1answer
33 views

MySQL ON DUPLICATE KEY UPDATE performance issue

We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like ...
0
votes
2answers
190 views

Slow insert - 300+ Million Records

The following query takes over an hour on the table to insert. There are no indexes. There was one which was deleted but still takes an hour after deletion. Any pointers on improving this ...
0
votes
1answer
27 views

Selecting 3MB (or more/less) data from database server

I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a ...
0
votes
0answers
11 views

How can I check the size of my SELECT query? PostgreSQL

I know how can I check the size of my database, called test: SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where datname='test'; Now, I ...
0
votes
1answer
41 views

Does having a big table that I do not query cause the whole DB to be slower?

I have a log table that I use for logging files sent to clients. I'm not querying that table, just use it for logging (INSERT) and when I need to check when something was sent to clients, usually once ...
4
votes
1answer
275 views

Negative number of rows inserted

I just ran a rather simple INSERT statement (that I think processed a lot of rows), and I got the message: -1,311,750,183 rows inserted Should I be concerned about the fact that it's negative?
0
votes
0answers
21 views

Innodb Deadlock During Insert

What could be the reason behind 2 inserts causing a deadlock? Here is what I get from log file: *** (1) TRANSACTION: TRANSACTION 6CA06B9, ACTIVE 6 sec inserting mysql tables in use 1, locked 1 LOCK ...
1
vote
1answer
35 views

Mysql super slow

I have a database of movies that I downloaded, it is around 51mb in size. One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad ...
0
votes
1answer
213 views

Inserting empty string on Windows MySQL doesn't use default value

Why inserting '' on a column with default integer value on MYSQL/WINDOWS produces ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1 The query is: insert into test2 (id,test) ...
1
vote
1answer
43 views

Mutating Error , How to solve it

CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER (10); ...
2
votes
1answer
57 views

What are possible causes for consistently volatile INSERT performance in MySQL tables?

I have a Java program running on a VM that is sequentially feeding a queue of single row INSERTs through a single connection to a MySQL server on the same VM. The queue execution is not artificially ...
3
votes
3answers
5k views

INSERT INTO without duplicates

I want to do this INSERT INTO AdminAccounts (Name) SELECT Name FROM Matrix But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.
0
votes
1answer
49 views

Create SP for inserting data from a table to a table

I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the ...
1
vote
1answer
53 views

Insert (write) performance

We are trying to speed up the queries in our application and in the process identified all queries which took more than 100ms. We noticed that there were few insert statements which took more than ...
0
votes
0answers
77 views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...
1
vote
2answers
68 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
1
vote
1answer
73 views

How to insert data into tables which reference each other?(MySQL)

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet). I'm not going to give my «homework» because I want to learn what I'm doing but I will ...
1
vote
1answer
50 views

Insert with different key values to avoid contraint violation

I have a set of Inserts traced of a database and I want to issue them into another similar one. The problem is that I'm getting contraint violation errors due the similar database already has the keys ...
1
vote
1answer
71 views

inserting large quantity of data into mysql table

I need to insert about 400 000 000 rows of test data into the InnoDB table. Table is quite simple: CREATE TABLE IF NOT EXISTS `tgt` ( `id_1` int(10) unsigned NOT NULL, `id_2` int(10) unsigned NOT ...
3
votes
2answers
2k views

“on duplicate key update” all the non-key fields mentioned in the insert

How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion? insert into tablename set keyname = 'keyvalue', fieldname = ...
1
vote
1answer
61 views

INSERT from a SELECT combined with a single value

I have this query that returns several rows of item_id: select item_id from properties where name like 'body'; And I have the second one that returns 1 row of tag_id: select id from tags ...
0
votes
1answer
148 views

Postgres: How to insert row with autoincrement id

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value. SELECT nextval('context_context_id_seq') The result is: 1, 2, 3,...20.... But ...
0
votes
1answer
89 views

MongoDB insert performance rapidly dropping

I'm currently experiencing a rapid decrease of the insert Performance. I have a rather simple data model { "Id": "ObjectId", "SomeProp": "xy", "SomeTags": [ { "key": ...
1
vote
1answer
61 views

LOAD DATA stuck at Null State MySQL

I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of ...
1
vote
1answer
55 views

How can I insert records 'into null' in Oracle?

I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL ...
4
votes
2answers
261 views

Sudden performance decrease in SQL Server

I've got a problem with a SQL Server 2008 R2 instance. Last week the performance suddenly broke down. All inserts are very very slow. It takes 4-5 times longer than before. On the hardware we found no ...
1
vote
1answer
69 views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
1
vote
1answer
92 views

Optimizing a large number of INSERT … SELECT statements

I have a MySQL InnoDB database with two tables related by a foreign key. Each time a row is inserted into one it uses an INSERT ... SELECT statement from the other to get the corresponding foreign ...
2
votes
2answers
1k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
1
vote
1answer
48 views

Problem with INSERT function

I use this function INSERT (http://www.smallsql.de/doc/sql-functions/string/insert.html) to add a value in a data: INSERT (log, POSITION(' edited' IN log), 1, 'NOW()') I use also the NOW function ...
-1
votes
1answer
89 views

Insert a file into PostgreSQL BYTEA field using PHP

The previous answers have been horribly complex for the scope of this project. I want to take the $_POST result as a variable and insert it into a BYTEA field of a table. $_SESSION['username'] = ...
1
vote
0answers
57 views

RETURNING syntax with multiple columns

Again, another question related to the answer of the one there: Insert with OUTPUT correlated to sub query table So I ended up with this query, written for SQL Server: CREATE TABLE tempIDs ( ...
1
vote
0answers
49 views

MULTIPLE INSERT VALUES with INSERT into another table

here a scenario to have a more clear picture. The DB is MySQL. I've 2 tables events and *event_users* : EVENTS id int type int date date time time ... EVENT_USERS event_id ...
2
votes
1answer
147 views

Insert with OUTPUT correlated to sub query table

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with ...
1
vote
1answer
99 views

GETDATE() uniqueness when used on INSERT

Recently I was reading this blog post: http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx which contains this section: Picking a Clustered Index There are a couple of ...
0
votes
1answer
60 views

Stored Procedure with ROW instead of a list of parameters

i have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement. i would like to know if it is possible to declare ...
0
votes
1answer
94 views

When and how would MyISAM concurrent insert = always be a problem?

I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table. We're talking about 10-20 inserts ...
0
votes
2answers
240 views

Inserting 60 000 rows taking too long

I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over. Shouldn't it be ...
2
votes
1answer
1k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
0
votes
2answers
35 views

Anyway to get the trigger to work?

I want to insert last insert query to the another column.For that i wish to use TRIGGERS -- -- Triggers `sync_log` -- DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`; DELIMITER // CREATE TRIGGER ...
0
votes
2answers
60 views

Insert into … (…) (Select…) on dupliacate key update

Can you help me to solve a problem. The task is to insert a record in MySQL database. terms: If the record exists - to update the record. If the record does not exist - to create a new one. The Key ...
1
vote
1answer
72 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
2
votes
1answer
116 views

Increment value in row if it exists, otherwise create row with value?

Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I ...
6
votes
2answers
2k views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...