Hot answers tagged

83

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


24

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


18

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


15

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


15

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


12

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


12

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


11

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


11

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


11

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


10

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


10

Three ways. Either IGNORE duplicate errors: INSERT IGNORE ... ; -- without ON DUPLICATE KEY or try to do a redundant update when there is a duplicate: INSERT ... ON DUPLICATE KEY UPDATE id = id ; or check for duplicates before inserting: INSERT INTO requests (id, ctg, msg, nick, filled, dated, filldate) SELECT (NULL, ...


10

Nothing is wrong with your table definition. (Except hat I would use jos_content_id or something instead of the non-descriptive column name id. And I probably would use text instead of varchar(50). Your INSERT statement is the problem. With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value ...


10

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest. Since you have the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but ...


9

I have three different ideas that I mentioned in a comment above. Here is a little elaboration on at least one of them (which you are stuck on due to self-diagnosed tunnel vision). Well, you could calculate the number of rows that make up 10% beforehand, and then compare that in your batch. I was thinking about this but we don't really need to ...


9

Suppose you have mydb.mytb and you want to create mydb.mytbcopy I have five(5) approaches to doing this copy APPROACH #1 In the mysql client, run the following USE mydb CREATE TABLE mytbcopy LIKE mytb; INSERT INTO mytbcopy SELECT * FROM mytb; APPROACH #2 MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" mysql ...


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

No, you don't need to gave the columns in the same order. Not least, table order may not reflect actual on-disk order (this is 100% true for SQL Server, and I'm sure MySQL is the same) Unless your OCD itch needs scratched


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


8

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


7

This could be as simple as one part of your code running a transaction: insert into t1... insert into t2... commit; while another part of your code modifies the same tables in a different order: delete from t2 where... delete from t1 where... commit; If both of those transactions run at the same time, a race condition can occur: the first transaction ...


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

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL. You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. Use COPY instead; see PgJDBC batch copy and the CopyManager. As for number of concurrent loaders: Aim for a couple ...


7

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though. Requires Postgres 9.0 or later (workaround for older versions possible). CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass) RETURNS void AS $func$ BEGIN FOR _tbl_in IN EXECUTE format('SELECT * FROM %s', pg_typeof(_tbl_in)) LOOP -- do ...


7

By definition, a table is an unordered set of rows. There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can say: INSERT dbo.table(column) SELECT 1; SELECT SCOPE_IDENTITY(); More generally, you can use the OUTPUT clause, which doesn't ...


7

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves. Anyway, the solution would look something like this: MERGE INTO Table1 AS t1 USING MyTable ON 1=0 -- always generates ...


7

Looking at your problem from an SSIS perspective I feel the reason this may have taken so long is that you didn't have batching on. This can lead to too many rows filling the SSIS pipeline and can hinder your SSIS performance as a result. What you need to do is alter your rows per batch setting and possibly your maximum insert commit size. Now what you set ...


7

I guess you could (ab)use MERGE for this. First create a (temporary) table: CREATE TABLE tempIDs ( PersonId INT, FinancialInstitutionId INT ) ; Then MERGE into Person (instead of INSERT), so you can use columns of the tables involved in the OUTPUT clause: MERGE INTO Person USING FinancialInstitution AS fi ON 1 = 0 WHEN NOT MATCHED THEN INSERT ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...



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