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
1k views

Insert a file into PostgreSQL BYTEA field using PHP

The previous answers have been horribly complex for the scope of this project. I want to take the $_POST result as a variable and insert it into a BYTEA field of a table. $_SESSION['username'] = ...
1
vote
0answers
148 views

RETURNING syntax with multiple columns

Again, another question related to the answer of the one there: Insert with OUTPUT correlated to sub query table So I ended up with this query, written for SQL Server: CREATE TABLE tempIDs ( ...
8
votes
1answer
6k 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 ...
1
vote
1answer
660 views

GETDATE() uniqueness when used on INSERT

Recently I was reading this blog post: http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx which contains this section: Picking a Clustered Index There are a couple of ...
0
votes
1answer
725 views

Stored Procedure with ROW instead of a list of parameters

i have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement. i would like to know if it is possible to declare ...
0
votes
1answer
329 views

When and how would MyISAM concurrent insert = always be a problem?

I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table. We're talking about 10-20 inserts ...
0
votes
2answers
391 views

Inserting 60 000 rows taking too long

I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over. Shouldn't it be ...
0
votes
2answers
39 views

Anyway to get the trigger to work?

I want to insert last insert query to the another column.For that i wish to use TRIGGERS -- -- Triggers `sync_log` -- DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`; DELIMITER // CREATE TRIGGER ...
1
vote
1answer
214 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
2
votes
1answer
1k views

Increment value in row if it exists, otherwise create row with value?

Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I ...
7
votes
1answer
17k 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` ...
6
votes
3answers
13k 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
553 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 ...
2
votes
2answers
790 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
153 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
5k 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
5k 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
100 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
161 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
147 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
8k 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
1k 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 ...
2
votes
1answer
3k 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
338 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 ...
5
votes
2answers
11k 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
29 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?
2
votes
1answer
22k 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
137 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
460 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
1answer
3k 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
195 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
587 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
1k 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
602 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 ...
3
votes
1answer
713 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 ...
3
votes
2answers
2k 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
356 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. ...
3
votes
2answers
537 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
619 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
5k 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
324 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 ...
1
vote
0answers
106 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 ...
1
vote
2answers
415 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
33 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 ...
15
votes
2answers
27k 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
13k 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
866 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
2k 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 ...
3
votes
1answer
810 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
493 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 ...