We help IT Professionals succeed at work.

MySQL procedure or function

Medium Priority
272 Views
Last Modified: 2012-05-06
Hi,

I would like to copy the records from table 'A' to table 'B'.
Table A:
timestamp | value | varname | file_h | file_o
Table B:
timestamp | blockid | value | varname | exported | file_h

Notice there are field to compute: blockid and exported

to compute field exported:
if the file_h is NULL, set exported to -1, else set it to 1

to compute the blockid here is some code:

DECLARE mytimestamp DATETIME;
SET mytimestamp = ADDTIME(    <<the timestamp from the table>>  , '0 00:15:00');
IF m = 0 THEN
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), '00')
ELSE
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), m)
END IF;

1. What do I need ? procedure, function, ... ?
2. Can you compelte some code.

Thank you for any help.
Comment
Watch Question

Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
   Hi!

Easiest would be to create an function with an timestamp as an input variable
and varchar as an output variable and

http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html



Regards,
   Tomas Helgi
CREATE FUNCTION THEBLOCKID (ts TIMESTAMP) RETURNS VARCHAR(10) 
BEGIN
DECLARE mytimestamp DATETIME;
DECLARE blockid = VARCHAR(10);
SET mytimestamp = ADDTIME(    <<the timestamp from the table>>  , '0 00:15:00');
IF m = 0 THEN
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), '00')
ELSE
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), m)
END IF;
return BLOCKID;
END;
    

Open in new window

Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
Sorry small error in code.

Regards,
   Tomas Helgi
CREATE FUNCTION THEBLOCKID (ts TIMESTAMP) RETURNS VARCHAR(10) 
BEGIN
DECLARE mytimestamp DATETIME;
DECLARE blockid = VARCHAR(10);
SET mytimestamp = ADDTIME(    ts  , '0 00:15:00');
IF m = 0 THEN
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), '00')
ELSE
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), m)
END IF;
return BLOCKID;
END;

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hi,

I do not need the blockid, I need a to copy records from a table to another.
Database Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
Then simply use

insert into table a(column,...) select (columns,...) from table b

and you could call the function in the select statement.

Regards,
    Tomas Helgi
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
sorry
insert into table b(column,...) select (columns,...) from table a  :)

Regards,
   Tomas Helgi
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.