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

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

Copy the track table records to the corresponding track_ according to the mac_id two digits. In the table

such as: mac_id=12345678910, the latter two-bit 10 corresponds to the table is track_10, the record is copied to track_10

To create a sub_track stored procedure implementation:

 --Create a stored procedure named Sub_track
CREATE PROCEDURESub_track ()
begin        DeclareIint;--defining the loop variable i    Set @imei =0;--define IMEI last two bits    Set @t_count=0;--define how many record variables the table has    SETI=0; SELECT COUNT(*) into @t_count  fromTrack--get the number of bars in a table whileI<= @t_countDo--Loops  SELECT  Right(MAC_ID,2) into @imei  fromTrackORDER  byGpstimeDESCLIMIT I,1;--get IMEI last two bit    Set @imei =CONVERT(@imei, signed);--Convert to Digital    --SQL Statements    Set @sql1="(Mac_id,mac_type,channel,type,x,y,gpstime,bvalid,speed,dir,s1,s2,s3,s4,ins_date,imagefi Le,battery)SELECTMac_id,mac_type,channel,type,x,y,gpstime,bvalid,speed,dir,s1,s2,s3,s4,ins_date,imagefil E,battery from TrackORDER  byGpstimeDESCLIMIT "; Set @inset_sql=CONCAT ("INSERT  intoTrack_ ",@imei,@sql1, I, ",",1);--stitching a complete INSERT SQL statement    PREPAREInsert_track from @inset_sql;--Pre-compilation    EXECUTEInsert_track;--Execute SQL statement    SetI=I+1;--End LoopEnd  while; Commit;End

--Execute Stored procedure

 call Sub_track ();

@flm

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