dongzhi4690
dongzhi4690
2012-09-25 14:32
浏览 54
已采纳

MySQL:使用单个SQL命令将数据库A中的多行插入到数据库B中

I have a database with table named 'Customers' which it contains customer's phone number. Its definitely more than one row.

On the other hand, I also have database named 'SMS' with a table in it, called 'Outbox'.

In order to show all phone number from 'Customers' table, I use this SQL query :

SELECT Customers.PhoneNumber FROM Customers WHERE PurchaseDate BETWEEN 2012-01-01 AND 2012-01-31;

Now, I need those phone numbers (multiple rows) to be inserted into 'Outbox' at once. Every phone number will receive same message. so, basically I just need to know how to use this INSERT query for multiple rows :

INSERT INTO Outbox (PhoneNumber, Message) VALUES (*Multiple PhoneNumbers Here*, 'Same Message To All Customers');

any idea how to do it? thanks before.

图片转代码服务由CSDN问答提供 功能建议

我有一个名为'Customers'的数据库,其中包含客户的电话号码。 肯定不止一行。

另一方面,我也有一个名为'SMS'的数据库,里面有一个表,叫做'Outbox'。

为了显示“客户”表中的所有电话号码,我使用此SQL查询:

  SELECT Customers.PhoneNumber FROM Customers WHERE PurchaseDate BETWEEN 2012-01-01  AND 2012-01-31; 
   
 
 

现在,我需要将这些电话号码(多行)同时插入“发件箱”。 每个电话号码都会收到相同的消息。 所以,基本上我只需要知道如何对多行使用这个INSERT查询:

  INSERT INTO Outbox(PhoneNumber,Message)VALUES(* Multiple PhoneNumbers Here *,'Same 给所有客户的消息'); 
   
 
 

知道怎么做吗? 谢谢。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • duanhao4909
    duanhao4909 2012-09-25 14:37
    已采纳

    I assume you want a separate row for each phone number. You can do this by combining INSERT with SELECT:

    INSERT INTO db2.Outbox (PhoneNumber, Message)
        SELECT Customers.PhoneNumber, 'Same Message To All Customers' as message
        FROM db1.Customers
        WHERE PurchaseDate BETWEEN 2012-01-01 AND 2012-01-31;
    
    点赞 评论
  • douxuan4556
    douxuan4556 2012-09-25 14:37
    INSERT INTO Outbox (PhoneNumber, Message) 
    SELECT Customers.PhoneNumber,  'Same Message To All Customers' as Message 
    FROM Customers 
    WHERE PurchaseDate BETWEEN 2012-01-01 AND 2012-01-31; 
    

    Should do you

    点赞 评论
  • dongqie7806
    dongqie7806 2012-09-25 14:40
    INSERT INTO dbX.table_a
        SELECT ...
            FROM dbY.table_b
            WHERE ...
    

    You don't have to specify anything in the inserts' value list if you make sure the selects' column list matches dbX.table_a entirely.

    点赞 评论
  • duagfgfn1981
    duagfgfn1981 2012-09-25 14:40

    you can use (mysql_fetch_array) see these links below:

    http://php.about.com/od/phpwithmysql/ss/mysql_php_2.htm

    http://www.w3schools.com

    点赞 评论

相关推荐