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

learn more… | top users | synonyms

2
votes
1answer
113 views

Why do these concurrent MySQL inserts deadlock?

I am baffled by this deadlock: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 150511 18:42:43 *** (1) TRANSACTION: TRANSACTION B8972CA4, ACTIVE 0 sec inserting mysql ...
0
votes
2answers
116 views

I need to insert data from one table to another after casting the values

i have #temp table containing all details to insert into table(named:Customer) The column names of two tables are exactly equal,but datatypes are different.So i need to cast it before insert into ...
5
votes
3answers
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 ...
0
votes
1answer
31 views

Insert but Don't Commit MySql

I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id ...
1
vote
1answer
23 views

Create one row for every user in a Postgres table

I have a bunch of rows of users in a users table, for example: | id | name | |----|-------| | 1 | Chris | | 2 | Max | | 3 | Steve | For each one of these users, I'd like to create a row in ...
-1
votes
1answer
60 views

How can I generate a script to choose only top 10 items from each table in SQL database?

I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating ...
0
votes
1answer
23 views

How to make a mysql statement for sent log

I have a table called "make_sent_log" CREATE TABLE IF NOT EXISTS `make_sent_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transaction_id` varchar(255) NOT NULL, `total_base` int(11) NOT ...
0
votes
1answer
215 views

MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

I have this mysql table: CREATE TABLE `codes` ( `code` bigint(11) unsigned NOT NULL, `allocation` int(11) NOT NULL DEFAULT '0', `used` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), ...
1
vote
2answers
255 views

Fastest way to insert data into mysql from csv

I have a dataset of 233,902,846 rows in a csv file. I want to load the data into mysql table. What is the most efficient way of transferring the data so that data insertion is the fastest. actually ...
2
votes
2answers
100 views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
1
vote
0answers
45 views

Unable to insert explicit value in table due to IDENTITY_INSERT is ON Error

I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns. I am writing syntax is as below: use ...
1
vote
1answer
33 views

Nightly Excel Spreadheet import into mssql database

I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and ...
0
votes
1answer
89 views

Increase SQL Server Performance

I have a fairly simple SQL Server 2014 Standard database with one main table. Here's the workload: I insert a row, do a few updates to it (up to 5 updates), and move on. I always update recent rows. ...
1
vote
1answer
18 views

How can I insert procedure results from server 1 into table in server 2?

The goal is to link our website to the database. The database the website will connect to will be different then our production db. In fact, a separate instance of SQL. How can this be structure to ...
0
votes
1answer
43 views

Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking

Assume column with the following definition product_id INT NULL NO_DEFAULT product_id_nn INT NOT NULL NO_DEFAULT and query insert into table (product_id,product_id_nn) values ...
5
votes
2answers
199 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
3answers
288 views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
1
vote
1answer
47 views

MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

Just curious on resource efficiency. If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), ...
1
vote
2answers
61 views

Create rows when selected rows exist

I haven't spent much time with SQL Server, and I can't seem to figure something out. I have 3 tables; user, usergroup, group. usergroup is a table that contains both a group id and a user id, where ...
2
votes
0answers
39 views

Improve postgreql insert performance when compared to Oracle. Low memory utilization by Postgresql threads

I am trying to improve the performance of Postgresql (version 9.2.3) inserts for a simple table with 1 bigint, 1 varchar, 1 float and 2 time stamps. A simple replication of my JDBC program is ...
1
vote
1answer
741 views

INSERT take too much time

I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections ...
0
votes
1answer
32 views

How to insert all the dates in a date range into Oracle database?

For instance, The date range is from 26th Jul to 1st Aug, that is 26th Jul, 27th Jul, 28th Jul, 29th Jul, 30th Jul, 31th Jul and 1st Aug How can I construct a function (of course, I need to ...
0
votes
1answer
72 views

Multi-threaded inserts vs serial

I have a large database application on SQL Server 2012 Standard Edition which receives data from hardware in chunks of 500-1000 (100 byte) rows every few seconds through a stored procedure. Would a ...
1
vote
1answer
45 views

How to update/insert/inject nodes into XML

I have an application which stores optional Site information in an XML field. Sample table: CREATE TABLE [dbo].[Sites]( [SiteID] [int] IDENTITY(1,1) NOT NULL, [SiteName] [nvarchar](80) ...
11
votes
2answers
17k 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 ...
10
votes
3answers
70k 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 ...
2
votes
1answer
154 views

What are possible causes for consistently volatile INSERT performance in MySQL tables?

I have a Java program running on a VM that is sequentially feeding a queue of single row INSERTs through a single connection to a MySQL server on the same VM. The queue execution is not artificially ...
2
votes
2answers
114 views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc ...
0
votes
0answers
63 views

ORA-00911: invalid character error in insert statement

I created a table called TROUBLE in Oracle Database 10g Express Edition by this command create table TROUBLE ( City VARCHAR2(13) NOT NULL, SampleDate DATE NOT NULL, Noon NUMBER(3,1), Midnight ...
2
votes
2answers
20 views

Insert query in mysql

I am using MySQL. I have table like below: CREATE TABLE test_table ( first_name VARCHAR(20), last_name VARCHAR(20), full_name VARCHAR(40) ); Now I want to insert values like below: ...
2
votes
2answers
5k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
1
vote
1answer
43 views

How to update row values with values from SELECT into another DB in a trigger

I have been working in a trigger for INSERT|UPDATE values for a given record based on same PK. What trigger basically should do it is, foreach row when some INSERT happens on veeva_new.account: ...
0
votes
0answers
20 views

Clob (Swift message) data in Oracle table

I have swift data in clob column and want to insert this data in multiple columns in different table. create table test_swift (id number , block4 clob ); insert into test_swift values (123, ...
4
votes
1answer
65 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 ...
1
vote
2answers
48 views

Postgresql: Insert trigger function fails on partial insert statement

We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test ...
3
votes
1answer
103 views

MySQL trigger after insert with insert and delete instructions does not work properly

I have the following trigger created in phpMyAdmin: Table: tb_agenda Time: AFTER Event: INSERT BEGIN INSERT INTO tb_realizacao (dt_agenda, titulo, ...
3
votes
3answers
208 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 ...
3
votes
2answers
307 views

INSERT data on MySQL to specific position based on its value (similar to ORDER BY)

I have a MySQL table with 3 columns for simplicity sake: Datename |Date |Name -------------+--------+----- 30/06/15test |30/06/15|test 30/06/15other|30/06/15|other Note: Column Datename is ...
2
votes
1answer
30 views

Persistant inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
2
votes
1answer
299 views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
3
votes
2answers
171 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] ...
0
votes
1answer
23 views

How can I connect my other table using the mysqli_insert_id?

I've got a problem like this , I cannot get the id of the parent table . table_name : transaction_tbl - transaction_id -file_name -file_path -description table_name : transaction_details ...
3
votes
1answer
129 views

Does Postgres preserve insertion order of records?

For example when I'm using query which returns record ids INSERT INTO projects(name) VALUES (name1), (name2), (name3) returning id; Which produce output: 1 2 3 Will this ids point to ...
0
votes
4answers
66 views

Insert from Another Table + Removing Duplicate Entries in Postgres

I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code. I have two tables I'm working with: mytesttable, which ...
0
votes
0answers
44 views

Can I simulate a sequence in pre-2012 SQL Server, with separate identity column, in function style?

A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK ...
0
votes
0answers
37 views

Oracle Linked Server on SQL partial insert

I am using OpenQuery to insert into OracleLinkedServer, inside a while loop from while ( @id <= @cnt) Begin INSERT INTO OPENQUERY ( MY_LINKED_SERVER,'SELECT COL1, ...
1
vote
1answer
17 views

JDBC prepared statement - make a token null

So, I have an insert statement in a Java program I'm writing. Under some conditions, I want it to insert some values as null. However, before I can execute the statement, I have to set all of the ...
0
votes
1answer
24 views

Table1 ID is auto increment and i also want to store the information from table 2 against that auto generated ID

This table has an auto increment ID on back end. "INSERT INTO `test`.`personal_info` (`username`, `email`,`password`) VALUES ('$username','$email','$password')"; This was for signing up of one ...
2
votes
1answer
41 views

Insert into a DB that was selected via other DB?

I have a table that stores what database should I use for a given device( specified by its IMEI), like this: imeiDB( IMEI VARCHAR(15), db VARCHAR(100)); Let's say I have the database ...
1
vote
0answers
180 views

Multi-Table Join to Single Destination [closed]

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 ...