mysql查询后生成一张表_mysql 查询记录并插入另一张表

从 bd_house 查询 houseId, houseName,   插入 bd_house_customer

DROP PROCEDURE IF EXISTS insert_extra_column_test;

DELIMITER //

CREATE PROCEDURE insert_extra_column_test()

BEGIN

-- 声明变量

DECLARE newId LONG;

DECLARE houseId LONG;

DECLARE houseName VARCHAR ( 50 );

DECLARE num integer;

-- 声明接收结果的游标

DECLARE

house CURSOR FOR

-- 查询记录

SELECT

id houseId,

NAME houseName

FROM bd_house

WHERE

id NOT IN ( SELECT house_id FROM bd_house_customer WHERE customer_id = 1111)

ORDER BY id;

-- 打开游标

OPEN house;

-- 匹配第一个游标, 给变量赋值

FETCH house INTO houseId, houseName;

-- 这是固定遍历 100 次, 遍历全部可以用 while ( house is not null ) 判断

SET num = 100;

WHILE ( num > 0 ) DO

INSERT INTO `bd_house_customer` (

`id`,

`tenant_id`,

`house_id`,

`house`,

`customer_id`,

`customer`,

`change_cause`,

`relation_state`,

`remarks`,

`lease_type`,

`belong_house_name`,

`sign_date`,

`lease_source`,

`asset_use_type`

)

VALUES

(

houseId,

1111,

houseId,

houseName,

1111,

'张三',

'2222',

'currentTenant',

1,

'NewRent',

houseName,

NULL,

'PC_terminal',

'zzzz'

);

SET num = num - 1;

-- 匹配下一个游标并赋值

FETCH house INTO houseId, houseName;

-- 结束循环, 关闭游标

END WHILE;

CLOSE house;

END //

DELIMITER ;

CALL insert_extra_column_test();