A core statement in SQL that does what it says on the tin: inserts data into a table.
-1
votes
0answers
35 views
which database suitable for huge inserts for analytics system [closed]
I want to develop a analytics system for very high traffic web sites, that records many behaviors of users. I want to choose a proper database for doing it well.
This database must suitable for huge ...
0
votes
2answers
41 views
Is there slowdown inserting into an InnoDB table that has no index set?
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.
Those tables only contain a few thousand rows.
Would it be faster to INSERT data ...
0
votes
1answer
12 views
content types of insertion
I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
0
votes
1answer
49 views
Large INSERTs performance optimization
I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query).
The queries are send ...
0
votes
0answers
39 views
When to split a large table
I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc).
However, in my use-case, every single day, the table gets 2160000 new rows.
It's also crucial that ...
2
votes
1answer
42 views
BULK INSERT Data from file but ignore the first occurence of field terminator
I have a file that has data arranged in the following style:
1 1 0.5102 0.4898 0.0000 0.0000 0.0000
1 2 0.3114 0.6886 0.0000 0.0000 0.0000
I need to load ...
-1
votes
2answers
184 views
Insert ignore with conditional
I need to insert some records which might have duplicates. Column to overwrite/skip is mytable.email and mytable.fooFlag should be 1.
I came up with something like this:
INSERT IGNORE INTO mytable ...
1
vote
3answers
85 views
How to handle update or insert situations
From time to time my stored procedures looks like
create procedure handle_data
@fk int
,@value varchar(10)
as
begin
if exists (select * from my_table ...
0
votes
1answer
28 views
Reinsert data in new table with new IDs
I have the following two tables:
buynsell
CREATE TABLE `buynsell` (
`id` VARCHAR(20) NULL DEFAULT NULL,
`msg` VARCHAR(255) NULL DEFAULT NULL,
`date` VARCHAR(25) NULL DEFAULT NULL,
...
1
vote
1answer
68 views
Insert from one row to another using cases
My original table was(being used since 2005):
CREATE TABLE `request` (
`msg` VARCHAR(150) NOT NULL,
`id` VARCHAR(20) NOT NULL,
`ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',
`date` ...
0
votes
1answer
89 views
On duplicate key do nothing
I am inserting into the following table using LuaSQL with PtokaX API.
CREATE TABLE `requests` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`ctg` VARCHAR(15) NOT NULL,
`msg` ...
3
votes
1answer
92 views
How this simple Stored Procedure ended up in Deadlock
Thanks for all and this forum is really helpful.
After few minutes of system running we get “Try restarting the transaction.”
And its end up in deadlock.
We use MySQL 5.5.28 and the Java code ...
2
votes
3answers
126 views
How can I store a pdf in PostgreSQL
I have to store .pdf files in a table.
I have a table, state, with columns:
id_state,
name,
pdffile (bytea)
I want to store the pdf files in the pdffile column.
How can I do this?
1
vote
3answers
119 views
Complex query with multiple normalized fields
We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into ...
2
votes
2answers
322 views
Check if row exists
I need to write a query that will insert a row only once, even if the query is run multiple times. Being new to SQL (well new-ish), I did an if not exists(...) but a friend said he preferred deleting ...
2
votes
1answer
80 views
How to improve INSERT speed in large table that has unique constraint
I have a simple MyISAM table:
explain entities;
+------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra ...
2
votes
2answers
941 views
Move data from one table to other in batch's
I have a table 18 Million records I need to add append it to another table (with the same structure) I tried the import-export wizard, the problem is that the log gets very very big and eventually it ...
3
votes
2answers
228 views
Subquery in the VALUES clause
Here's a real-world schema:
CREATE TABLE [dbo].[Setting]
(
[ID] [BIGINT] NOT NULL,
[Module] [NVARCHAR](400) NOT NULL,
[Property] [NVARCHAR](400) NOT NULL,
[Value] ...
2
votes
0answers
138 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
1
vote
0answers
57 views
Have @Table1 with values to update into @Table2, can I update @Table1 with @Table2.@@Identity? or Cursor?
I have 2 tables:
declare @Customer_Links table (
Customer_LinkID int identity(1,1),
CustomerID int
)
declare @Customer_Regions table (
CustomerRegionID int identity(1,1),
Customer_LinkID int,
...
0
votes
0answers
41 views
row locking or avoiding duplicate insertion on primarykey/otherfield combo
i have a table that stores additional data pertaining to files/images saved in files elsewhere, each entry can be associated with another type in the system,
for users there may be one or more ...
1
vote
1answer
819 views
MySQL Insert into two tables using new IDs
I'm trying to select data already in the database and insert it into two tables using the generated ID from table one.
table1 contains configuration options, table2 contains pricing data. Table2 uses ...
3
votes
1answer
183 views
Adding new key(s) to enable partitioning, INSERT performance?
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 diferent devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
1
vote
1answer
48 views
INSERT trigger dying with with #target columns not equal to #expressions, but they are equal
I am trying to write my first trigger. It is an insert trigger as follows:
<b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
2
votes
3answers
1k 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.
1
vote
3answers
1k views
ON DUPLICATE KEY UPDATE question
I'm trying to compare data from two systems. The basic idea is:
Create Temp Table.
Insert Table1 Data.
Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected.
Filter where ...
2
votes
2answers
112 views
INSERT succeeds but all inserted values become NULL
I tired to execute an insert directly from phpMyAdmin as follows:
INSERT INTO oracle.PLAYLIST_MUSIC ( TID,
ID,
STATUS,
CREATED_BY,
...
0
votes
2answers
154 views
Is there a SQL Server equivalent to “OVERRIDING USER VALUE”
While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries.
Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to ...
4
votes
1answer
2k views
Optimal way to ignore duplicate inserts?
Background
This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:
-- Ignores duplicates.
INSERT INTO
db_table ...
3
votes
3answers
277 views
InnoDB insertion faster
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
3
votes
1answer
504 views
Generate insert statements for table AND related data
I'm looking to move data from tables and related tables between 2 databases. The trouble is the primary keys are out of sync.
Is anyone aware of a tool which can generate insert statements for a ...
2
votes
3answers
1k views
How can I configure MySQL Innodb to handle 1000s of inserts per hour?
I have a very high traffic website where it is possible that 1000s of new records are inserted every hour.
This one error is crippling the site:
PDOException: SQLSTATE[40001]: Serialization failure: ...
1
vote
1answer
140 views
processlist showing '%' as host from trigger
I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:
CREATE TRIGGER access_insert_trg BEFORE INSERT ...
0
votes
0answers
153 views
Slow INSERT INTO SELECT statement – not repeatable!
Short Version:
INSERT INTO SELECT into a table with fulltext index with the same data takes sometimes 25 seconds and sometimes 2500 seconds. We have no idea where this huge gap is coming from.
Long ...
4
votes
1answer
196 views
Select one name per person where spelling variations exist
I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
1
vote
2answers
74 views
How can I append a column to an inner SQL statement?
I have two tables that have the same structure except one has an additional column that should be auto-incremented. Sequence and trigger are defined.
I want to copy all data from one table to ...
1
vote
0answers
133 views
Multi-Table Join to Single Destination
I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL.
To ...
0
votes
1answer
74 views
myISAM data loss at DELETE / INSERT
I have a linked table id_group | id_user. Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the ...
1
vote
4answers
304 views
Limiting Generated Sequence Values in Multi-table Insert
In Oracle, given the following objects:
create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;
Suppose I populate table a as follows:
...
2
votes
1answer
344 views
Fastest way to copy data from MyISAM to InnoDB
I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.
However, every approach I try ...
1
vote
1answer
384 views
“Mysql has gone away” heavy multiple insert
I am stuck because Mysql server will go away after executing this part of code this function will update the database with the new RSS feeds which are stored in ToUpdate but Mysql stops working. Every ...
3
votes
1answer
1k views
How do you read MySQL InnoDB Monitor output?
I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs.
The log says that "transaction 1 is rolled back". Why is this the ...
3
votes
2answers
877 views
Mysql load from infile stuck waiting on hard drive
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
2
votes
1answer
365 views
Derby slows down after 1.2 million records
I'm using a Derby database, mass insertions slow down to 1/4 to 1/6 the speed once there are 1.2 million records (about 6 GB database size, one major table, other tables are tiny). Is that normal? Is ...
2
votes
1answer
338 views
Extract some columns data from one table and insert into multiple tables
I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables
tbl_1: id, name, lastname
tbl_2: id, ...
2
votes
2answers
2k views
How does mysql decide if concurrent insert is possible for a MyISAM table?
For "concurrent inserts", MySQL reference manual has the following explanation:
The MyISAM storage engine supports concurrent inserts to reduce
contention between readers and writers for a given ...
1
vote
2answers
1k views
MySQL - INSERT INTO … SELECT … UNION
I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a ...
2
votes
1answer
294 views
Help with a complicated MySQL Query inserting data using select from 2 tables
I am stuck with a case and unable to get the desired result from my query..
I have to insert into a table some records based on multiple records from 2 different tables..
Tables: Target: ...
2
votes
1answer
1k views
sqlcmd slow against SQL Server 2008 R2
I am trying to import a longish SQL script (3.5 millions of lines, 1,5 GB in size) containing mainly short INSERT INTO statements with data using sqlcmd -E -d <database> -S 127.0.0.1 -i ...
1
vote
5answers
4k views
Mysql: Insert performance INNODB vs MYISAM
I am inserting into a simple small table with 5 attributes and 1000 rows.
I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds.
I changed the engine to MYISAM, then the ...
