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

learn more… | top users | synonyms

3
votes
1answer
4k 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
3answers
5k 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 ...
0
votes
1answer
234 views

SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables

I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes. I have written a VB.NET program that handles the generation of row ...
1
vote
2answers
214 views

Return ID from partitioned table in postgres?

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm ...
0
votes
0answers
50 views

Overcoming the HDD IOPS limits on a billion row table? Is getting SSD the only solution?

I have a table with this structure: CREATE TABLE `requests` ( `userid` int(11) NOT NULL, `messageid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `problem_id` int(3) NOT NULL, ...
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 ...
0
votes
1answer
1k views

Declare variable mysql

I am new to mysql, I have been using SQL in the past. I am trying to insert rows into a table via creating a loop. But struggling with it. Any help? DECLARE @minid INT = (SELECT MIN(Product_ID) FROM ...
0
votes
3answers
80 views

prepared statements are not inserted

i am new to JDBC and trying following code. Could not find the error in it try{ Connection con = ConnectionProvider.getCon(); stmt=con.prepareStatement("insert into ...
0
votes
0answers
55 views

Inserting and updating on same table

I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has ...
0
votes
2answers
77 views

calling a function with parameters in insert

Is it possible in SQL ellegantly achieve the following: insert into my_table (the_geom) VALUES geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)), ...
0
votes
1answer
535 views

INSERT / UPDATE to merge data, when empty data is an empty string

Consider a table of some family members: mysql> SELECT * FROM some_table; +--------+--------+--------+ | f_name | l_name | work | +--------+--------+--------+ | Meirav | Cohen | school | | ...
0
votes
1answer
219 views

Insert but ignore if duplicate AND set a value different if exist in another table

I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array ...
1
vote
1answer
478 views

Large INSERT INTO SELECT [..] FROM gradually gets slower

I've written a program that does a large INSERT in batches of 100,000 and shows its progress. The source table contains 2.5GB of data: CREATE TABLE wikt.text ( old_id INT(10) UNSIGNED NOT NULL ...
1
vote
1answer
131 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
3
votes
2answers
5k 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 ...
0
votes
1answer
28 views

content types of insertion

I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
0
votes
1answer
4k views

How to extract the last inserted row in SQL Server?

When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
0
votes
1answer
67 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
0
votes
1answer
99 views

Party Relationship Model in Practice - Insertions

I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community. Here is a basic representation of what I'm doing (in pseudo sql): Parties - ID, ...
1
vote
0answers
200 views

SQL Server 2008R2 Merge Replication Identity Range Exceptions for Data Imports

Bottom line question: How can I insert data into subscriber tables that are governed by identity range management, where the data has valid identity values that fall outside of the current range? ...
1
vote
1answer
754 views

filling a table by triggering multiple tables on insert

I have a table m contains the following fields: id int(11) not null auto_increment, //PK name varchar(100) null, something_else varchar(100) default 'not important' I need to fill up table m by ...
4
votes
1answer
171 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 ...
3
votes
3answers
139 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 ...
2
votes
0answers
527 views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
0
votes
2answers
179 views

What am I doing wrong in mysql database?

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message: MYSQL ERROR: Can't update table 'player_viplist' in ...
2
votes
1answer
214 views

Using the auto increment id from one query as the fk of the next insert

I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees. I have two tables, both have auto incrementing ID's. Table 1: ID - auto field - int other field - varchar ...
-1
votes
2answers
1k views

How to get missing values in row order?

In a table, many rows have been deleted, how to get the id of missing rows for next INSERT? For example id col1 1 1 3 3 4 4 5 5 8 8 9 9 how can I get the ...
3
votes
2answers
729 views

Bulk insert with format file: 0 rows affected

When using the BULK INSERT command in SQL Server 2008 it returns: (0 row(s) affected) I am using this command to carry out the bulk insert: BULK INSERT Test FROM 'C:\DataFiles\Tests.dat' ...
3
votes
1answer
187 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. ...
2
votes
2answers
178 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'); ...
1
vote
0answers
384 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
2
votes
1answer
795 views

DB2 Communication link failure issue

I would like to get your valuable suggestions for the below problem. My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data ...
1
vote
1answer
170 views

DB2 Insert and Deadlocks

My application has 4 different threads doing inserts into the same DB2 table simultaneously. Could this cause a deadlock / lockwait situtation? Theoretically I don't see a problem here; as one ...
0
votes
0answers
82 views

Any drawback to lots of creating and renaming tables at InnoDB?

I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk ...
2
votes
0answers
239 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
2answers
267 views

Is there slowdown inserting into an InnoDB table that has no index set?

I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such. Those tables only contain a few thousand rows. Would it be faster to INSERT data ...
1
vote
1answer
28 views

Plan for storing transactions in an easily retrievable manner, indefinitely

I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the records as financial transactions. Clients need to be able to view these ...
4
votes
3answers
16k 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
5k 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 ...
2
votes
1answer
5k views

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ...
0
votes
2answers
395 views

Is there a SQL Server equivalent to “OVERRIDING USER VALUE”

While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries. Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to ...
0
votes
1answer
606 views

Large INSERTs performance optimization

I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query). The queries are send ...
0
votes
0answers
137 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
3
votes
1answer
256 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 ...
-1
votes
2answers
327 views

Insert ignore with conditional

I need to insert some records which might have duplicates. Column to overwrite/skip is mytable.email and mytable.fooFlag should be 1. I came up with something like this: INSERT IGNORE INTO mytable ...
1
vote
3answers
152 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 ...
0
votes
1answer
40 views

Reinsert data in new table with new IDs

I have the following two tables: buynsell CREATE TABLE `buynsell` ( `id` VARCHAR(20) NULL DEFAULT NULL, `msg` VARCHAR(255) NULL DEFAULT NULL, `date` VARCHAR(25) NULL DEFAULT NULL, ...
1
vote
1answer
454 views

Insert from one row to another using cases

My original table was(being used since 2005): CREATE TABLE `request` ( `msg` VARCHAR(150) NOT NULL, `id` VARCHAR(20) NOT NULL, `ctg` VARCHAR(10) NOT NULL DEFAULT 'misc', `date` ...
3
votes
1answer
382 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 ...
2
votes
3answers
863 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?