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

learn more… | top users | synonyms (1)

1
vote
2answers
108 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
10k 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
1k 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 ...
2
votes
1answer
488 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 ...
1
vote
1answer
15k 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 ...
1
vote
1answer
1k 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 ...
2
votes
1answer
4k 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": "...
3
votes
1answer
712 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
69 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 ...
1
vote
1answer
196 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 ...
2
votes
1answer
621 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
592 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 key....
3
votes
1answer
200 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 ...
1
vote
1answer
3k 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) ...
-2
votes
1answer
1k 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
238 views

MULTIPLE INSERT VALUES with INSERT into another table [duplicate]

I have 2 tables, events and event_users: Events id int type int date date time time Event_users event_id int user_id int user_role int ... Events can have ...
1
vote
0answers
176 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 ( ...
9
votes
1answer
8k 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
857 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 ...
5
votes
2answers
1k 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 ...
0
votes
1answer
936 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
2answers
465 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 ...
6
votes
1answer
20k 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
39 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 `...
2
votes
2answers
4k views

Insert into … (…) (Select…) on duplicate 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. ...
1
vote
1answer
239 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']; store_in_db_via_PDO(...
2
votes
1answer
1k 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 ...
0
votes
1answer
612 views

SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables

I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes. I have written a VB.NET program that handles the generation of row ...
1
vote
1answer
60 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 ...
14
votes
2answers
33k 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 ...
1
vote
1answer
1k 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 ...
3
votes
2answers
936 views

Return ID from partitioned table in postgres?

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm ...
4
votes
1answer
12k 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
2answers
563 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
0answers
182 views

Overcoming the HDD IOPS limits on a billion row table? Is getting SSD the only solution?

I have a table with this structure: CREATE TABLE `requests` ( `userid` int(11) NOT NULL, `messageid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `problem_id` int(3) NOT NULL, `...
0
votes
3answers
102 views

prepared statements are not inserted

i am new to JDBC and trying following code. Could not find the error in it try{ Connection con = ConnectionProvider.getCon(); stmt=con.prepareStatement("insert into ...
4
votes
1answer
6k views

Move values from one table to another one via stored procedure in sql server 2008

I have two tables Login (userid, username, password) audit_login (userid, Transaction_Date, Status (success or failed) Now I want create a stored procedure in which, if anyone wants to login then ...
0
votes
0answers
232 views

Inserting and updating on same table

I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has ...
1
vote
2answers
169 views

calling a function with parameters in insert

Is it possible in SQL ellegantly achieve the following: insert into my_table (the_geom) VALUES geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)), geomfromwkb('\...
0
votes
1answer
10k views

INSERT / UPDATE to merge data, when empty data is an empty string

Consider a table of some family members: mysql> SELECT * FROM some_table; +--------+--------+--------+ | f_name | l_name | work | +--------+--------+--------+ | Meirav | Cohen | school | | ...
0
votes
1answer
1k views

Insert but ignore if duplicate AND set a value different if exist in another table

I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array ...
2
votes
1answer
4k views

Large INSERT INTO SELECT [..] FROM gradually gets slower

I've written a program that does a large INSERT in batches of 100,000 and shows its progress. The source table contains 2.5GB of data: CREATE TABLE wikt.text ( old_id INT(10) UNSIGNED NOT NULL ...
1
vote
1answer
366 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
0
votes
1answer
423 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 ...
2
votes
1answer
26k views

How to extract the last inserted row in SQL Server?

When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
2
votes
2answers
8k 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 $$ ... $$ ...
0
votes
1answer
151 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
0
votes
1answer
521 views

Party Relationship Model in Practice - Insertions

I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community. Here is a basic representation of what I'm doing (in pseudo sql): Parties - ID, ...
1
vote
1answer
4k views

filling a table by triggering multiple tables on insert

I have a table m contains the following fields: id int(11) not null auto_increment, //PK name varchar(100) null, something_else varchar(100) default 'not important' I need to fill up table m by ...
0
votes
1answer
6k views

Declare variable mysql

I am new to mysql, I have been using SQL in the past. I am trying to insert rows into a table via creating a loop. But struggling with it. Any help? DECLARE @minid INT = (SELECT MIN(Product_ID) FROM ...