Tag Info

Hot answers tagged

39

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


13

One common approach: Disable / drop indexes / constraints on target table. INSERT dbo.[Target] WITH (TABLOCKX) SELECT ... With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this ...


11

Plain INSERT INSERT INTO bar (description, foo_id) SELECT val.description, f.id FROM ( VALUES ('testing', 'blue') ,('another row', 'red' ) ,('new row1', 'purple') -- purple does not exist in foo, yet ,('new row2', 'purple') ) val (description, type) LEFT JOIN foo f USING (type); The use of a LEFT [OUTER] JOIN ...


10

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


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

Your syntax is almost good, needs some parenthesis around the subqueries and it will work: INSERT INTO bar (description, foo_id) VALUES ( 'testing', (SELECT id from foo WHERE type='blue') ), ( 'another row', (SELECT id from foo WHERE type='red' ) ); Tested at SQL-Fiddle Another way, with shorter syntax if you have a lot of values to insert: ...


8

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


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


7

Suppose I have to export data from one server to another. Best is to use IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance. Executing ...


6

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


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

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)


6

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


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

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.


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

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


5

As Aaron mentioned, for some reason you need to terminate each statement in Fiddle. Anyway, for the query, you can use CROSS APPLY to expand the result set. This method scans the base table only once, and for each row applies the subquery, which in this case actually selects two rows: INSERT INTO userContact(userId, contactType, contactInfo) SELECT ...



Only top voted, non community-wiki answers of a minimum length are eligible