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

learn more… | top users | synonyms

0
votes
1answer
29 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?
1
vote
1answer
8k 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?
0
votes
1answer
78 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
173 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
1k 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 ...
4
votes
1answer
173 views

Disk usage spikes: good, bad or indifferent?

I'm working on a project where we're receiving data from multiple sources, that needs to be saved into various tables in our database. Fast. I've played with various methods, and the fastest I've ...
3
votes
3answers
156 views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
2
votes
0answers
721 views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
0
votes
2answers
246 views

What am I doing wrong in mysql database?

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message: MYSQL ERROR: Can't update table 'player_viplist' in ...
2
votes
1answer
333 views

Using the auto increment id from one query as the fk of the next insert

I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees. I have two tables, both have auto incrementing ID's. Table 1: ID - auto field - int other field - varchar ...
3
votes
2answers
1k views

Bulk insert with format file: 0 rows affected

When using the BULK INSERT command in SQL Server 2008 it returns: (0 row(s) affected) I am using this command to carry out the bulk insert: BULK INSERT Test FROM 'C:\DataFiles\Tests.dat' ...
3
votes
1answer
209 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
3
votes
2answers
274 views

Possible to have nested inserts in Postgres 8.4?

I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity): create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other'); ...
1
vote
0answers
485 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
2
votes
1answer
2k views

DB2 Communication link failure issue

I would like to get your valuable suggestions for the below problem. My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data ...
1
vote
1answer
194 views

DB2 Insert and Deadlocks

My application has 4 different threads doing inserts into the same DB2 table simultaneously. Could this cause a deadlock / lockwait situtation? Theoretically I don't see a problem here; as one ...
0
votes
0answers
90 views

Any drawback to lots of creating and renaming tables at InnoDB?

I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk ...
2
votes
0answers
464 views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
0
votes
2answers
316 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 ...
1
vote
1answer
29 views

Plan for storing transactions in an easily retrievable manner, indefinitely

I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the records as financial transactions. Clients need to be able to view these ...
8
votes
2answers
8k views

Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient? Executing in source server: INSERT INTO ...
2
votes
1answer
6k views

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ...
0
votes
2answers
531 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 ...
0
votes
1answer
878 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
170 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 ...
3
votes
1answer
401 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
394 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
158 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
43 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
676 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` ...
3
votes
1answer
461 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
1k 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
436 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
600 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
419 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
2k 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 ...
4
votes
2answers
1k 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
464 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 ...
0
votes
0answers
100 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 ...
3
votes
1answer
2k 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
257 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 ...
2
votes
1answer
218 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 ...
1
vote
3answers
3k 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
212 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, ...
12
votes
1answer
15k 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
2k 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
1k 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 ...
4
votes
3answers
5k 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
514 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 ...
4
votes
1answer
575 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 ...