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
SQL Server Indexes - Ascending or Descending, What Difference Does It Make
Calculating Difference Between Two Timestamps in Oracle in Milliseconds
MySQL, Better to Insert Null or Empty String
How to Replace a String in a SQL Server Table Column
How to Create a Sequence in MySQL
SQL to Json - Array of Objects to Array of Values in SQL 2016
How to Store Only Time; Not Date and Time
Sqlite - How to Join Tables from Different Databases
SQL Selecting from Two Tables With Inner Join and Limit
Real Life Example, When to Use Outer/Cross Apply in Sql
Default Row Order in Select Query - SQL Server 2008 VS SQL 2012
SQL Query to Pivot a Column Using Case When
Using Excel Vba to Export Data to Ms Access Table