New answers tagged insert
0
After some digging, I managed to successfully copy history and trends from one item to another.
The sort of syntax I used is this:
History table:
INSERT INTO history (`itemid`, `clock`, `value`, `ns`)
SELECT '<new_itemid>', `clock`, `value`, `ns`
FROM history
WHERE itemid = <old_itemid>;
Trends table:
INSERT INTO trends (`itemid`, `clock`, `num`,...
1
Column < Row < Block < Table
One column had a number, date, string, or a small number of other possibilities. It takes a few bytes or many bytes.
One row like you described (4 columns each taking a few bytes) will take perhaps 40 bytes when you add in some overhead.
One block is 16KB and holds (in your case) a few hundred rows. I mention a block ...
0
INSERT INTO new_table
(id, ...)
SELECT id + 1000, ...
FROM old_table;
This assumes the target table has ids no larger than 1000; adjust if needed.
3
You can use a data modifying common table expression:
with deleted as (
DELETE FROM some_table
WHERE id = 42
returning user_id
)
INSERT INTO other_table(user_id, "timestamp")
select user_id, timestamp '2020-01-04 21:32:34'
from deleted;
answered Jan 4 at 20:24
a_horse_with_no_name
60.4k1212 gold badges115115 silver badges150150 bronze badges
Top 50 recent answers are included
Related Tags
insert × 694mysql × 220
sql-server × 166
postgresql × 144
performance × 74
trigger × 69
update × 64
oracle × 47
select × 40
innodb × 34
bulk-insert × 30
t-sql × 27
stored-procedures × 27
database-design × 24
sql-server-2012 × 21
sql-server-2008 × 20
foreign-key × 19
sql-server-2014 × 17
mariadb × 14
query × 14
transaction × 14
deadlock × 14
sqlite × 14
index × 13
delete × 13