如何使用SQL从两个数据库中获取数据并插入到临时表中？

Question

我有两个数据库old和new。我想对两个数据库中的记录进行计数，并将它们的表名插入到新的临时表中。

我创建了临时表：

CREATE TEMPORARY TABLE tempemp (tablename varchar(50),northwindcount int(11),dest_northwindcount int(11));

和统计的记录：

select (select count(*) from northwind.orders) as northwind_cnt, (select count(*) from dest_northwind.orders) as dest_northwind_cnt;

如何插入到临时表中？

Answer 1

不知道MySql，但在MS SqlServer T-SQL中，您会这样做：

INSERT INTO
    Tempemp(
       tablename,northwindcount,dest_northwindcount)
SELECT 
    '???' AS table make,
        (SELECT COUNT(*) FROM Northwind.Orders)
       AS Northwindcount,
    (SELECT COUNT(*) FROM Dest_Northwind.Orders) 
        AS dest_northwindcount

我的语法可能有点错，但是一般的想法是INSERT语句可以有SELECT子句而不是VALUES子句。也许在MySql中也有类似的东西？

Answer 2

我为此编写了过程，现在它起作用了。谢谢你们所有人。

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE newcount INTEGER;
DECLARE oldcount INTEGER;
DECLARE oldtableName char(50);
DECLARE newtableName char(50);

DECLARE curs1 CURSOR FOR SELECT TABLE_NAME,SUM(TABLE_ROWS) as count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dest_northwind' AND 
TABLE_NAME IN(select table_name from information_schema.tables
where table_schema='dest_northwind') GROUP BY TABLE_NAME ;

DECLARE curs2 CURSOR FOR SELECT TABLE_NAME,SUM(TABLE_ROWS) as count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'northwind' AND 
TABLE_NAME IN(select table_name from information_schema.tables
where table_schema='northwind') GROUP BY TABLE_NAME ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curs1;
OPEN curs2;
read_loop: LOOP
FETCH curs1 INTO newtableName,newcount;
FETCH curs2 INTO oldtableName,oldcount;
IF done THEN
LEAVE read_loop;
END IF;
SELECT newtableName,newcount,oldcount;
insert into zreult1(Tablename,Northwind,Dest_Northwind)values(oldtableName,oldcount,newcount);
END LOOP;
CLOSE curs1;
CLOSE curs2;
END