Hot answers tagged insert
26
Column order does matter so if (and only if) the column orders match you can for example:
insert into items_ver
select * from items where item_id=2;
Or if they don't match you could for example:
insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;
but relying on column order is a bug waiting to happen (it can change, as ...
9
As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert.
This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted).
If you find ...
9
Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works:
create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );
declare
r v_tq84_virtual_test_with%rowtype;
begin
select * into r from v_tq84_virtual_test_with where col_2 = 8;
r.col_4 := r.col_4 - 2;
...
9
Why no clustered index? Why no primary key?
This is most likely your problem: you don't have any order to the table (in the sense of, say, an IDENTITY column) this you are inserting into a heap
See
http://stackoverflow.com/q/5094400/27535 (SO)
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/14/are-inserts-quicker-to-heap-or-clustered-tables.aspx ...
8
It the optimiser decides it would be more efficient to sort the data prior to insert, it will do so somewhere upstream of the insert operator. If you introduce a sort as part of your query, the optimiser should realise that the data is already sorted and omit doing so again. Note the execution plan chosen may vary from run to run depending on the number of ...
8
I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead).
I tried to make different results comparable by using more than one ...
7
The ORDER BY clause in the SELECT statement is redundant.
It is redundant because the rows that will be inserted, if they need to be sorted, are sorted anyway.
Let us create a test case.
CREATE TABLE #Test (
id INTEGER NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id);
CREATE TABLE #Sequence (
number INTEGER NOT NULL
);
INSERT ...
6
Why insert into tblusers directly at all?
I always use staging tables. You can use SSIS of course for the same result at with greater complexity
INSERT INTO [staging].[Users]
([username], [password])
VALUES ('user1', 'pass1'),
('user2', 'pass2')
INSERT INTO [tblUsers]
([username], [password])
SELECT DISTINCT [username], [password] ...
6
Upsert statements used to be planned for 9.1 but have been postponed to 9.2, so until then, your only choice is to test if the value already exists before inserting.
Alternatively, if your intention is merely to have a unique identifier, you could simply use a sequence + nextval.
If you want to create a function to do that, this should get you started:
...
6
The code you have inherited is broken - and it always has been broken. That re-factoring you'd like to avoid needs to be done. There is no alternative to an explicit order by to guarantee the sort order of a result set and there never has been.
Who knows if the code you inherited always returned rows in the order the original developer 'expected' or not.
6
Some ideas:
Inject some GO commands every thousand or few thousand lines. Then instead of one ginormous batch it is broken up into multiple batches.
Change your individual INSERT statements to INSERT ... VALUES () with a thousand sets each.
Use transactions and commit and/or checkpoint gratuitously (again, every 1000 inserts or so is probably a good place ...
6
You are dealing with a deadlock, not a performance bottleneck issue.
If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.
Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in ...
6
Your insert syntax is wrong, using column=value in the values clause doesn't do what you think it does.
Try:
INTO oracle.PLAYLIST_MUSIC ( TID,
ID,
STATUS,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE,
`ORDER`
)
VALUES(56919,
...
6
As mentioned in the comments...
There is no reason to DELETE/INSERT instead of just UPDATE or checking with EXISTS, but there are some reasons NOT to
More IO to remove a record and add a new one than an in-row update
Depending on clustered index you may increase fragmentation
Log file growth
Increased locking on the rows in question - EXISTS is about as ...
5
I suspect you want to put the whole thing in an anonymous PL/SQL block and run that, i.e.
BEGIN
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', NULL);
INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
INSERT INTO ...
5
It seems pretty easy:
postgres=# create table inet_test (address inet);
CREATE TABLE
postgres=# insert into inet_test values ('192.168.2.1');
INSERT 0 1
postgres=# insert into inet_test values ('192.168.2.1/24');
INSERT 0 1
postgres=# select * from inet_test;
address
----------------
192.168.2.1
192.168.2.1/24
(2 rows)
5
Are there any other snags I should be aware of that might result in an
insert, update, or deletion of a record not incrementing this value?
ora_rowscn is always incremented when a row changes - but in a default configuration it can also be incremented when a row does not change
If you need to check the whole table for udates, one method is to use ...
5
A lot of folks added a lot of good points in the comments.
1) Separate your transaction logs onto a different drive. That's going to be tough with a laptop. If you can't do that, get yourself an SSD for the laptop, and that should make your life considerably better.
2) Pre-grow your data and log files to a target amount. If you expect to add 1GB of data ...
5
The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes.
When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin. Do the math on ...
5
Is your database large enough? Are you sure your INSERT don't trigger auto-growth? If your deployements don't have Instant File Initialization enabled then this is exactly the behavior one would expect when a database file growth is triggered: random blocking of writes for the duration of file growth and initialization. You could also be experiencing log ...
5
While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:
we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?
Yes (emphasis mine):
If ...
5
It can't be done because you're mixing curval and nextval (happy to be proven incorrect, by the way). You also can't use WITH xxxx AS in an INSERT ALL, which was my first thought as a way around this.
Anyway, this is a logical workaround for you:
insert all
when mod(x,2) = 0 then
into b (val) values (my_seq.nextval/2)
into c (val) ...
5
A slightly more efficient way (which will do at worst one seek/scan instead of two against the existing data):
UPDATE dbo.whatever SET ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
INSERT dbo.whatever ...
END
MERGE may be tempting, however there are a few reasons I shy away from it.
the syntax is daunting and hard to memorize
you don't get any more ...
4
Getting the max(ora_rowscn) will require a full table scan each time you do it. It may be faster just to refresh the entire cache each time.
It sounds like you need a way to notify the other service that a change took place and what the change was. You could maintain a log table with a column that indicates which system needs to consume the change. The ...
4
Some things you can look at...
Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is).
Try turning on IO Stats to see just how much IO the FK lookups are taking.
What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)?
Lets start here and see where we go.
4
Change the table's definition by adding a UNIQUE KEY constraint on the combination of the two columns:
CREATE TABLE contacts
(
id int auto_increment primary key,
name varchar(20),
network_id int,
network_contact_id int,
CONSTRAINT network_id_contact_id_UNIQUE
UNIQUE KEY (network_id, network_contact_id)
);
You ...
4
otherwise, insert my_seq.currval (the most recently generated value but not a new one) into c.val
This only means something if you have in mind a certain order that the rows in a will be processed (otherwise what is "the most recently generated value but not a new one"?). I've assumed they will be processed in descending order so that we hit an even ...
4
You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names:
INSERT INTO TargetTable
(Emp_ID, Name)
SELECT Emp_ID, MIN(Name)
FROM SourceTable
GROUP BY Emp_ID ;
And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on ...
4
Experimentally, your code worked for me on both 10.50.1600 (2008 R2 RTM) Developer and 10.0.4000 (2008 SP2) Express. However, it failed against 9.0.5057 (2005 SP4 + hotfix). So, I dug into the documentation.
The 2005 version says this about expressions in the VALUES clause (emphasis mine):
expression
Is a constant, a variable, or an expression. The ...
4
Some people find the code for DELETE-followed-by-INSERT easier to follow especially if some rows are to be UPDATEd as well as some INSERTed, but I would recommend avoiding it. If you do use this method, make sure this or your overall process is wrapped in a transaction so that if something goes wrong nothing happens (or you might delete a row then fail to ...
Only top voted, non community-wiki answers of a minimum length are eligible
