A core statement in SQL that does what it says on the tin: inserts data into a table.
17
votes
1answer
35k 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 ...
13
votes
3answers
4k 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, ...
13
votes
4answers
2k views
How can I tell WHY an insert on a certain table is slow?
I know that an INSERT on a SQL table can be slow for any number of reasons:
Existence of INSERT TRIGGERs on the table
Lots of enforced constraints that have to be checked (usually foreign keys)
Page ...
5
votes
2answers
564 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)
...
5
votes
2answers
2k views
BULK INSERT - best usage
I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table.
I split ...
5
votes
1answer
110 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 ...
4
votes
3answers
888 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 ...
4
votes
3answers
4k 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 ...
4
votes
2answers
3k views
Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)
Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn.
First, I do this:
select max(ora_rowscn) from ...
4
votes
1answer
3k 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 ...
4
votes
1answer
231 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
1answer
1k views
nest SELECT in INSERT
I'm trying to do something like:
INSERT INTO stories
(vid, ip, body, timestamp)
VALUES (
(SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1),
INET_ATON('127.0.0.1'),
'test',
...
4
votes
1answer
2k 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', ...
4
votes
1answer
215 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 ...
4
votes
1answer
652 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 ...
3
votes
3answers
3k 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 ...
3
votes
3answers
209 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])
...
3
votes
2answers
3k 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,
...
3
votes
2answers
2k views
How Does Oracle Handle Multiple Concurrent INSERTs Against One Table
I am trying to understand Oracle 11g a little more closely. My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time.
For ...
3
votes
1answer
2k 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 ...
3
votes
2answers
255 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] ...
3
votes
3answers
295 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
2answers
928 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 ...
3
votes
2answers
416 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 ...
3
votes
1answer
97 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 ...
3
votes
3answers
126 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 ...
3
votes
1answer
69 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 ...
3
votes
1answer
551 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 ...
3
votes
1answer
184 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 ...
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 ...
2
votes
3answers
2k 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: ...
2
votes
2answers
333 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
2answers
117 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,
...
2
votes
2answers
956 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 ...
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 ...
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 ...
2
votes
1answer
105 views
After dropping the clustered index, it is still attempting to do a clustered insert
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 ...
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.
2
votes
2answers
1k 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 = ...
2
votes
1answer
372 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
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;
...
2
votes
3answers
147 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?
2
votes
1answer
88 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
1answer
359 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 ...
2
votes
1answer
395 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
302 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
0answers
160 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
4answers
307 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:
...
1
vote
3answers
88 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 ...
1
vote
3answers
185 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
...
