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

learn more… | top users | synonyms

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 ...
2
votes
1answer
221 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
221 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, ...
15
votes
1answer
16k 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
6k 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
528 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
592 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
113 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
169 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
120 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
652 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: ...
3
votes
1answer
642 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
764 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 ...
4
votes
1answer
3k 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
2k 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
1k 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
969 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
4k 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
3k 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
636 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: ...
3
votes
1answer
3k 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 ...
4
votes
5answers
10k 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 ...
6
votes
2answers
8k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
1
vote
4answers
482 views

cross self join to get more rows - better solution?

I have a table employees: +----------------+------------------+ | Field | Type | +----------------+------------------+ | id | int(10) unsigned | | persons_id | ...
2
votes
1answer
143 views

After dropping the clustered index, it is still attempting to do a clustered insert [closed]

I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows. I am running the following script to do it: USE ...
4
votes
3answers
10k views

Slow insert in SQL Server 2008

Our products' database contains an audit trail table: CREATE TABLE gn_AuditTable( gn_ObjectId int NULL, gn_Action smallint NULL, gn_Time datetime NULL, gn_UserId int NULL, gn_Login ...
1
vote
1answer
181 views

In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT?

I have this small, simple table: CREATE TABLE `ads_tracker` ( `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `type` smallint unsigned NOT NULL, `reference` smallint unsigned NOT NULL, ...
1
vote
1answer
172 views

Overloaded Database - Batch Updates vs Atomic/Single Updates

We have on overloaded database which is now receiving failed transactions consequently. The proposed solution is to create a 2nd database that taskes all the (ecommerce) transactions initially and ...
5
votes
2answers
1k views

Speed up INSERTs

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is : Have no index on the table whatsoever Switch to simple logging (from full logging) ...
3
votes
2answers
2k views

How to INSERT row in another database/schema in MySQL

Let's say I have the following database/schema setup with the following tables: SCHEMA_1 (default): TABLE_1 SCHEMA_2: TABLE_2 How do I insert rows in another schema's ...
4
votes
1answer
911 views

Does the order of columns has a significant effect on INSERT?

Do we experience a noticeable difference in performance for these two queries? INSERT INTO table (col1, col2, col3, col4, col5) ... and INSERT INTO table (col5, col3, col1, col2, col4) ... Do we ...
4
votes
3answers
3k views

Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other ...
3
votes
2answers
3k views

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?

MySQL version: 5.5.13 The query that I'm using: SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT; ...
5
votes
1answer
127 views

Find out which columns must be specified in insert-statement

Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail. I wrote this query to find out which ...
1
vote
2answers
638 views

Deny insert/delete for all users in SQL Server 2005

In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one ...
1
vote
1answer
592 views

Insert bulk Parent and Children rows using SQL Query

We receive bulk data from our customers in a spread sheet. I loaded them in a temporary table as of now. I tried to normalize the data and create a parent table and each parent to have 4 or 5 child ...
4
votes
3answers
6k views

How can I insert if key not exist with PostgreSQL?

I have a table: CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY) Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the ...
1
vote
1answer
645 views

Doing a bulk insert in SQL Server

I have to do the insert operation in the db as follows, I create an item I create 5-10 properties for that item, all these data i get in the form of a collection of objects. I donot want to ...
15
votes
3answers
12k views

Efficient INSERT INTO a Table With Clustered Index

I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER. E.G.: INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) SELECT TRACKING_NUMBER, COL_B, ...
0
votes
1answer
2k views

INSERT INTO remote table on different server with OUTPUT clause gives error

I have a procedure which makes one or two inserts into a remote table. To accomplish the second insert, I need to know the recently inserted Id of the first insert. To make this work my insert clause ...
3
votes
3answers
137 views

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted? For instance, I'm splitting payments and charges to their own tables from a previously unified ...
1
vote
1answer
503 views

Concurrent INSERTs in XtraDB (InnoDB)

I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows. CREATE TABLE ...
3
votes
3answers
235 views

Checking for dupes on Compact Insert Statement

I'm not sure if that is the correct term "Compact Insert Statement" It is just what I have always heard it referred to. It goes as follows: INSERT INTO [tblUsers] ([username], [password]) ...
5
votes
1answer
4k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
36
votes
1answer
81k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
1
vote
3answers
316 views

Insert to particular location in Oracle DB table?

Suppose I have a table containing the following data: Name | Things ------------- Foo | 5 Bar | 3 Baz | 8 If I want to insert a row, so that the final state of the table is: Name | Things ...
4
votes
1answer
3k views

Getting the “ORA-00911” error when I am trying to insert more than 1 row

Lets say I have the follow insert statements. INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL); INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', ...