SQL stored procedure instance-dynamically copies data from one table to another based on table data.

SQL stored procedure instance-dynamically copies data from one table to another based on table data.

Dynamically copy data from one table to another based on the table data

Copy the records in the track table to the corresponding track _? Table Based on the two digits after mac_id _? Table

For example, if mac_id = 12345678910, the last two 10 corresponding tables are track_10, and the record is copied to track_10.

Create a sub_track stored procedure:

-- Create procedure sub_track () begin declare I int; -- Define the cyclic variable I set @ imei = 0; -- Define the last two sets of imei @ t_count = 0; -- defines the number of record variables in the table SET I = 0; SELECT COUNT (*) INTO @ t_count FROM track; -- Get the number of tables while I <= @ t_count do -- loop select right (mac_id, 2) INTO @ imei FROM track order by gpstime desc limit I, 1; -- Get the last two sets @ imei = CONVERT (@ imei, SIGNED) of imei; -- CONVERT to numbers -- SQL statement set @ sql1 = "(mac_id, mac_type, channel, type, x, y, gpstime, bvalid, speed, dir, s1, s2, s3, s4, ins_date, imagefile, battery) SELECT mac_id, mac_type, channel, type, x, y, gpstime, bvalid, speed, dir, s1, s2, s3, s4, ins_date, imagefile, battery FROM track order by gpstime desc limit "; set @ inset_ SQL = CONCAT ("insert into track _", @ imei, @ sql1, I, ",", 1 ); -- concatenate the complete insert SQL statement PREPARE insert_track from @ inset_ SQL; -- pre-compile EXECUTE insert_track; -- EXECUTE the SQL statement set I = I + 1; -- end the loop end while; commit; end -- execute the Stored Procedure CALL sub_track (); @ flm

Summary

The above is an example of the SQL stored procedure introduced by Alibaba Cloud. It dynamically copies data from one table to another based on the table data. I hope this will help you, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!