New answers tagged insert
1
APPROACH #1
If you never delete from table1, table2, or table3
STEP #1
Using a Cartesian Join, generate a script that makes individual inserts
SQL="SELECT CONCAT('INSERT IGNORE INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 ...
0
I think that if you dont care about FOO'and can freely drop it, just drop it and make a CTAS using parallel, it will be the fastest. Use the alter sessions sugested by Balazs Papp, maybe add a third one (force parallel DDL).
3
Improving INSERT Performance with Direct-Path INSERT
Example:
alter table foo nologging;
alter session force parallel query parallel 8;
alter session force parallel dml parallel 8;
insert /*+ append */ into foo select * from bar where some_unimportant=condition;
4
Use the format() function with placeholder for literals: %L
select format('insert into table values(%s, %L, %L, %s, %s, %L, %L, %L)',
id, bnrcno, chargetype, domno, coa_id, liability_gl, revenue_gl, taxno)
from bill_charges_map
where domno=5;
For numeric columns (integer, numeric, float etc) use %s as the placeholder. For all other types ...
-1
select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||revenue_gl||','||taxno||')' from bill_charges_map where domno=5
I think what you're looking for is this:
insert into table1
select id, bnrcno, chartype, domno, coa_id, liability_gl, revenue_gl, taxno
from bill_charges_map
where domno = ...
0
I believe you are seeing two different errors on your history report.
You should validate the step output file on the job step(s).
You should validate the linked server permissions & authentication.
Recreating the step output error
Testing with a location that does not exist
Create a jobstep that tries to access a step output folder location that ...
3
You can create a function and use it as the default for the column:
create function f() returns text language sql as $$
SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM generate_series(1, 20)
$$;
create table foo(id serial primary key, url_prefix text not null ...
2
According to the documentation:
In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.
Therefore, a more direct approach would be:
INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid`
ON DUPLICATE KEY UPDATE ...
0
I suspect the answer goes something like this...
Approach 1: 800KB takes a lot of disk activity to write. This takes perhaps 1000ms. When you "wrote" a row in 31ms, that was because of most of the I/O being delayed until after acknowledging the action. When it took 718ms, the disk was busy writing other things, so it was slow at accepting the current ...
Top 50 recent answers are included
