如何使用 SQL 从两个数据库中获取数据并插入到临时表中? [英] How to fetch data from two db and insert into temp table, using SQL?

问题描述

我有两个数据库old 和new.我想从两个数据库中计算记录并用它们的表名插入新的临时表.

I have two databases old and new. I want to count record from both the databases and insert into new temporary table with their table name.

我创建了临时表:

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;

如何插入临时表?

推荐答案

不知道 MySql，但在 MS SqlServer T-SQL 中，你会做这样的事情:

Dunno about MySql, but in MS SqlServer T-SQL, you'd do something like this:

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 中有类似的东西?

My syntax might be a little off, but the general idea is that an INSERT statement can have a SELECT clause instead of a VALUES clause. Maybe something similar in MySql?

这篇关于如何使用 SQL 从两个数据库中获取数据并插入到临时表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！