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

learn more… | top users | synonyms

35
votes
1answer
74k 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 ...
15
votes
3answers
10k 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, ...
14
votes
4answers
5k 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 ...
12
votes
1answer
14k 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 ...
8
votes
2answers
7k 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 ...
7
votes
2answers
4k 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 ...
6
votes
2answers
7k 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, ...
5
votes
3answers
6k 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 ...
5
votes
1answer
3k 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 ...
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) ...
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
125 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 ...
5
votes
1answer
1k 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 ...
4
votes
3answers
25k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
4
votes
3answers
9k 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 ...
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 ...
4
votes
3answers
5k 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
1answer
800 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
2k 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
344 views

Negative number of rows inserted

I just ran a rather simple INSERT statement (that I think processed a lot of rows), and I got the message: -1,311,750,183 rows inserted Should I be concerned about the fact that it's negative?
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', ...
4
votes
2answers
963 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] ...
4
votes
1answer
537 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
619 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 ...
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 ...
4
votes
2answers
344 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 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
2k 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
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: ...
3
votes
3answers
152 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 ...
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]) ...
3
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 ...
3
votes
2answers
252 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'); ...
3
votes
1answer
2k 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 ...
3
votes
4answers
552 views

Insert into select into multiple related tables using INSERT_IDENTITY

Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 ...
3
votes
1answer
3k 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) ...
3
votes
3answers
6k 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.
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
2answers
6k 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
6k 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
2answers
2k 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 = ...
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
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 ...
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 ...
3
votes
2answers
99 views

Insert only deadlocks

We have an application inserting data into a table. Unfortunately, we are getting deadlocks and the deadlocks are coming from inserts only. We are seeing the inserts take key locks in a different ...
3
votes
1answer
439 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
1answer
603 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 ...
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; ...
3
votes
3answers
136 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
203 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. ...