MySQL :: Insert into Table, Data from Another Table

mySQL :: insert into table, data from another table?

INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)  
SELECT campaign_id, from_number, received_msg, date_received
FROM `received_txts`
WHERE `campaign_id` = '8'

Insert data from one table to another table in chunks - MySQL

Transaction you don't really that is why i removed it, the rest ist controlled by LIMIT and OFFSET which you need to do such a partial updates in block

CREATE TABLE test_arc (id int,check_time datetime, updated datetime)
CREATE TABLE test_data (id int,check_time datetime, updated Datetime)
INSERT INTO test_data VALUES (1,NOW() - INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY),(2,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY)
,(3,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY),(4,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY)
,(5,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY),
(6,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY),(7,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY)
,(8,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY)
,(9,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY),(10,NOW()- INTERVAL 1 DAY,NOW()- INTERVAL 1 DAY)
SELECT * FROM test_data 

id | check_time | updated
-: | :------------------ | :------------------
1 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
2 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
3 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
4 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
5 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
6 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
7 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
8 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
9 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
10 | 2022-02-12 18:27:12 | 2022-02-12 18:27:12
CREATE PROCEDURE `insert_data` ()
BEGIN
DECLARE i BIGINT DEFAULT 0;
DECLARE limitSize INT DEFAULT 5;
DECLARE maxnumber BIGINT;

SET maxnumber = (SELECT COUNT(*) FROM test_data) + 1;

WHILE i <= maxnumber DO
INSERT INTO test_arc
SELECT id ,check_time, NOW() FROM test_data
WHERE check_time>='2022-02-05'
ORDER BY updated ASC
LIMIT i, limitSize
ON DUPLICATE KEY UPDATE id =VALUES(id), check_time=VALUES(check_time),updated = Values(updated ); SET i = i + limitSize;
END WHILE;
END


CALL insert_data();
SELECT * FROM test_arc

id | check_time | updated
-: | :------------------ | :------------------
1 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
2 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
3 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
4 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
5 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
6 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
7 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
8 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
9 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12
10 | 2022-02-12 18:27:12 | 2022-02-13 18:27:12

db<>fiddle here

MySQL Insert INTO using SELECT from another table with additional data

You would just include the constant in the select list:

INSERT INTO tableA(field1, field2, field3)
SELECT field1, field2, 'somevalue'
FROM tableB
WHERE id = '1';

Also, don't surround the columns in a select in parentheses.

MySQL: insert values in new table from an old one using another mapping table

You could do an update join with the help of FIND_IN_SET:

UPDATE new_product_to_category t1
INNER JOIN new_mapping t2
ON FIND_IN_SET(t1.product_id, t2.old_cats) > 0
SET t1.category_id = t2.new_cat;

If you really wanted to avoid using a join, you could also phrase this update using a correlated subquery:

UPDATE new_product_to_category t1
SET category_id =
(SELECT t2.new_cat FROM new_mapping t2
WHERE FIND_IN_SET(t1.product_id, t2.old_cats) > 0);

How to insert records of a table into another table having same structure but different database?

Use simple INSERT statement (database_name.[schema_name].table)

INSERT [NewDB].[your_schema].[City](Id,Name) 
SELECT id, Name
FROM [OldDB].[your_schema].[City]
where _your where clause_

you can see here:
Insert data from db to another db

and here:How to insert table values from one database to another database?

Insert into ... values ( SELECT ... FROM ... )

Try:

INSERT INTO table1 ( column1 )
SELECT col1
FROM table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

  • Oracle
  • MS SQL Server
  • MySQL
  • Postgres
  • SQLite v3
  • Teradata
  • DB2
  • Sybase
  • Vertica
  • HSQLDB
  • H2
  • AWS RedShift
  • SAP HANA
  • Google Spanner

MySql: Insert data from one table to another

What you need is to update the table B and not insert new rows.

Use a join in the UPDATE statement:

UPDATE B
INNER JOIN A ON A.Tag = B.Tag AND A.Name = B.Name
SET B.Size = A.Size;

Insert an specific row from a table in a database A to another table in a database B

Assuming both the table contain the same columns (col1, col2, ...) and the db are on the same server
for copy a row you should use update .. not insert

UPDATE pacaya_control_venta.tb_venta_pacaya  a
INNER JOIN llamadas.tb_venta_pacaya b ON a.id=33 and b.id = 48
SET a.col1 = b.col1,
a.col2 = b.col2,
.....

insert is for create a new row not for updated existing one

INSERT INTO pacaya_control_venta.tb_venta_pacaya 
SELECT *
FROM llamadas.tb_venta_pacaya
WHERE ID = 48;


Related Topics



Leave a reply



Submit