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

learn more… | top users | synonyms

44
votes
1answer
112k 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 ...
18
votes
1answer
25k views

Optimal way to ignore duplicate inserts? [duplicate]

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 ...
16
votes
3answers
17k 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, ...
16
votes
4answers
8k 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 ...
11
votes
2answers
19k 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 ...
10
votes
2answers
15k 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 ...
9
votes
3answers
66k 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 ...
7
votes
2answers
9k 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, ...
7
votes
1answer
178 views

unable to insert row at id, but row does not exist

Here is the weird problem i am facing. I am trying to enter data using following query insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, ...
6
votes
1answer
3k 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 ...
5
votes
3answers
408 views

SQL Server clustered index, index balancing and insert performance using NewID

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE(). The connection pool for connections to this database/table rises as high as 50 on average ...
5
votes
3answers
10k 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
3answers
7k 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: ...
5
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 ...
5
votes
1answer
1k 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 ...
5
votes
1answer
5k 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
1answer
13k 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` ...
5
votes
2answers
2k 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
135 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 ...
5
votes
1answer
1k 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 ...
4
votes
3answers
13k 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
5k 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
5answers
13k 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 ...
4
votes
1answer
11k 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) ...
4
votes
4answers
7k 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
4answers
12k 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 ...
4
votes
3answers
180 views

INSERT/UPDATE in same query from previous SELECT with some singularities

I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,`first`,`last`,email) SELECT ...
4
votes
1answer
4k views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
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
144 views

Null Values in a CASE statement

I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to ...
4
votes
1answer
525 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
2answers
8k 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 ...
4
votes
1answer
5k 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
2k 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
778 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
64 views

How to only update if nothing has changed? I need to validate several things before I insert

Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A ...
4
votes
1answer
71 views

Prevent Inserts without Column List

We have a database running on SQL 2008 R2, in 2000 Compatibility mode. Is there a way to configure the database so that implicit insert statements (i.e. those without a column list) are invalid? I ...
4
votes
1answer
753 views

InnoDB inserts 100 times slower than SQL Server inserts

I have a simple table with two columns: id (autonumber) and name (char(255). When I use C# to insert 100 records in a loop it takes about 30 milliseconds per insert. This is way too slow. So I ...
4
votes
2answers
177 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 ...
4
votes
1answer
185 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
798 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

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
1answer
4k 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
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
169 views

order of inserting record is mismatching

Please check the below table structure and records to be inserted in that table. CREATE TABLE [tabGeneraltable]( id int identity, [codGenLedger] [uniqueidentifier] NOT NULL, [codInvoice] ...
3
votes
3answers
256 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
241 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
451 views

Efficently Remove 90% of a table's data

I am working on a process to remove 90% of a table's data because only 10% is needed for testing. The best way I have found includes storing 10% of the table's rows into a temporary table. Current ...