CODEWITHSUNDEEP
mysqlstored-procedures
Joe Ijam
Joe Ijam

Reputation: 2179

Mysql stored procedure to get the total count after insertion

I'm stuck on how to get incremented id and max, min, sum or count from the previous insertion statement.

Can anyone advice me how to do that in simple way?

CREATE PROCEDURE INSERTRECORD()
BEGIN
INSERT INTO tb_normalized_data_20110615
SELECT * FROM tb_normalized_data WHERE 
       date_added BETWEEN '2011-06-15 01:10:00' and '2011-06-15 01:19:59'

-- Stuck here how to get the value for(x_min_id, x_max_id, x_min_date,
--  x_max_date) from the statement above without querying again?

INSERT INTO tb_backup_tracker(min_id, max_id, min_date, max_date)
VALUES(x_min_id, x_max_id, x_min_date, x_max_date);

END;

Upvotes: 1

Views: 519

Answers (2)

cwallenpoole
cwallenpoole

Reputation: 82048

I think you're looking for a cursor. Basically, here's how it could work:

DECLARE cur CURSOR FOR SELECT * FROM tb_normalized_data WHERE 
       date_added BETWEEN '2011-06-15 01:10:00' and '2011-06-15 01:19:59';
DECLARE ID INT;
DECLARE DT DATE;/* Declare all of your columns */
DECLARE MIN_ID INT;
DECLARE MAX_ID INT;
DECLARE MIN_DATE DATE;
DECLARE MAX_DATE DATE;
-- add your other columns here...
BEGIN
  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO ID, DT /* Fetch into all of your columns */;
    IF ID < MIN_ID THEN
       SET @MIN_ID = ID;
    IF ID > MAX_ID THEN
       SET @MAX_ID = ID;
    END IF;
    INSERT INTO tb_normalized_data_20110615 (ID, DATE_ADDED 
           /*, rest of your columns*/ ) VALUES( /* columns */ );
  END LOOP;
  INSERT INTO tb_backup_tracker(min_id, max_id, min_date, max_date)
      VALUES(min_id, max_id, min_date, max_date);
  CLOSE cur;

Upvotes: 1

Bohemian
Bohemian

Reputation: 425198

Use a cursor to loop through the data and inside the cursor loop both insert and keep track of min/max values for whatever you need.

Also, your code is brittle: avoid the pattern INSERT INTO TABLE SELECT * FROM ...; if the second table adds a column your SQL will break. It's good practice to name the columns explicitly anyway.

Post if you need help with the cursor.

Upvotes: 0

Related Questions