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

learn more… | top users | synonyms (1)

5
votes
3answers
3k 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
41 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 ...
2
votes
1answer
59 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
205 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
26 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 ...
3
votes
2answers
5k 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
99 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
51 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 ...
1
vote
1answer
27 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
195 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 ('','')...
6
votes
2answers
371 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
471 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
205 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
73 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 ...
1
vote
0answers
133 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
1k 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
363 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
206 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
862 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) NULL,...
14
votes
2answers
34k 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 ...
2
votes
2answers
2k 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
2k 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 NUMBER(...
2
votes
2answers
24 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: ...
1
vote
1answer
125 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
72 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
77 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
122 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
301 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
419 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
1k 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
624 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
212 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
43 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 -...
0
votes
4answers
271 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
171 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 ...
1
vote
1answer
103 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
41 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
74 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 device1(IMEI:...
1
vote
0answers
184 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 ...
1
vote
0answers
250 views

Tuning a Postgres INSERT only database

I am designing a database which will have many inserts and selects, but no updates. Are there any considerations to take (in order to have high query performance) while knowing no tuple updates will ...
0
votes
0answers
52 views

Update a field in table when a combination of three fields with a second table is matched

I have a database form an old archaeological project which I imported into a mysql DB. I have a table "graves" with 400+ entries: "graves" id necr burials single multi 1 1 1 ...
4
votes
1answer
79 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 ...
1
vote
0answers
67 views

MySQL bulk deletions

I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb. The table I want to delete from can be any size, but will usually be ~...
-5
votes
1answer
110 views

how to insert into tables in sql server? [closed]

I have a query to create "governorate table" like this: CREATE TABLE governorate ( ID int IDENTITY(1,1) PRIMARY KEY not null , name varchar(50) UNIQUE , ); then I want to insert data in ...
0
votes
1answer
509 views

Mysql How to insert a variable as field value in a table

I have created a variable named hash as a password hash. How can this variable be inserted a a field value in a table? hash = pbkdf2_sha256.encrypt("password", rounds=200000, salt_size=16) cur....
2
votes
1answer
441 views

MySQL auto increment column increases after insertion error occurs

I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row ...
0
votes
2answers
76 views

Insert query with a subquery

I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly ...
1
vote
2answers
2k views

Merge faster than Insert

Are there any advantages/disadvantages of making use of a merge over a insert function? Can an insert statement make use of the `OUTPUT' Note: the ON clause for the merge is set to 1=0 (always insert)...
5
votes
2answers
16k views

Copy from one MySQL table to another MySQL table of same database

I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
0
votes
2answers
630 views

MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster?

I'm running an ETL process and streaming data into a MySQL table. Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck. Anyway, it's a basic insert/ update ...