mysql中把一个表的数据批量导入另一个表中 
 
 
 
 
 
  

   2013-03-08 22:54 
    43030人阅读 
   评论(3) 
   收藏 
   举报 
  
 
 
 
 
 
  

    mysql中把一个表的数据批量导入另一个表中
  
 
  

     
  
 
  

    不管是在网站开发还是在应用
   程序开发中，我们经常会碰到需要将MySQL或MS SQLServer某个表的数据批量导入到另一个表的情况，甚至有时还需要指定导入字段。 
  
 
  

    本文就将以MySQL
   数据库为例，介绍如何通过SQL命令行将某个表的所有数据或指定字段的数据，导入到目标表 中。此方法对于SQLServer数据库，也就是T-SQL来说，同样适用 。
  
 
  

     
  
 
  

    类别一、 如果两张张表（导出表和目标表）的字段一致，并且希望插入全部数据，可以用这种方法：（此方法只适合导出两表在同一database）
  
 
  

     
  
 
  

    INSERT INTO 目标表 SELECT * FROM 来源表;
  
 
  

    例如，要将 articles 表插入到 newArticles 表中，则可以通过如下SQL语句实现：
  
 
  

     
  
 
  

    INSERT INTO newArticles SELECT * FROM articles;
  
 
  

    类别二、 如果只希望导入指定字段，可以用这种方法：
  
 
  

     INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
  
 
  

    请注意以上两表的字段必须一致（字段类型），否则会出现数据转换错误。
  
 
  

     
  
 
  

    1、跨服务器复制表中数据
  
 
  

    insert into openrowset('sqloledb','localhost';'sa';'123',Test.dbo.Table_B) 
  
 
  

    select * from Test.dbo.Table_A 
  
 
  

     
  
 
  

    //启用Ad Hoc Distributed Queries：
  
 
  

     
  
 
  

    exec sp_configure 'show advanced options',1
  
 
  

    reconfigure
  
 
  

    exec sp_configure 'Ad Hoc Distributed Queries',1
  
 
  

    reconfigure 
  
 
  

     
  
 
  

    //使用完成后，关闭Ad Hoc Distributed Queries：
  
 
  

    exec sp_configure 'Ad Hoc Distributed Queries',0
  
 
  

    reconfigure
  
 
  

    exec sp_configure 'show advanced options',0
  
 
  

    reconfigure
   
  
  
 
  

    2、//不夸服务器
  
 
  

    insert into dbo.Table_B) select * from dbo.Table_A 
  
 
  

    将表名和数据库连接字符串用代码拼接好 然后执行上述您需要的sql语句 程序功能即可完成
  
 
  
 
   
 
  
 
  
 
   
 如何将一个mysql数据库中的一个表导入到另一个mysql数据库中
 
   
 
  
 
  
 
   
db1为原数据库，db2为要导出到的数据库，fromtable 是要导出的表名

1.方法一：
  登录导出到的数据库，执行
  create table fromtable select * from db1.fromtable;
2.方法二：
  在cmd下执行，mysqldump -u root -p db1 fromtable file=d:/fromtable.sql; 输入秘密，root为用户名
  登录db2 执行 source d:/fromtable.sql;
3.方法三：
  登录db1 执行 select * from fromtable into outfile "d:/fromtable .txt"; 导出纯数据格式
  登录db2 执行 load   data   infile   d:/fromtable .txt   into   table   fromtable; 需要先建一张和原表结构一样的空表。
4.建一个odbc连接，先导出到access中，再导出到另一个库中。
   
 
  
 
  
 
   
 
  
 
 
 
 
 
  
  
  
  
  
  
 
 
 
 
  

   
    顶 
   
   

    4 
   
  
 
  

   
    踩 
   
   

    0 
   
  
 
 
 
 

    
 
 
 

   
 
 
 
上一篇大批量插入数据时优化SQL语句 
下一篇如何提升MySQL批量插入的效率 
 
 
 
 
 
  
 
  
 
   

      相关文章推荐 
   
 
   
 
    
•  MYSQL使用mysqldump导出某个表的部分数据 
•  大数据技术实战线上峰会--董西成 
•  mysql 快速复制数据库 
•  30天系统掌握机器学习--唐宇迪 
•  mysql将一个表字段内容赋值给另一个表字段 
•  ORACLE数据库学习资料集锦 
•  mysql 把一个表中数据更新到另一个表 
•  PHP从零开始实战篇 
 
    
•  MySQL中如何将一个数据库的表导入到另一个数据库表中 
•  玩转微信小程序第一篇 
•  mysql 批量修改表中字段数据成不同的值 用一个表更新另一个表的方法 
•  深度学习案例分享—人脸检测 
•  mysql中把一个表的数据批量转移到另一个表中 
•  Sql Server 导入另一个数据库中的表数据 
•  mysql用存储过程将一个表中的数据跟新到另一个表中 
•  MySql中把一个表的数据插入到另一个表中的实现代码 
 
   
 
  
 
 
 

以下实例都是从本地导入：
 
hive> load data local inpath 'sales_info.txt' overwrite into table sales_info partition(dt='2019-04-26');
 
导入后本地的原数据文件依然存在，相当于复制过去；如果是从hdfs导入，则原数据文件不存在，相当于剪切过去。
 
实例1：
 
建表语句（不带分区字段，用逗号分隔）：
 
CREATE TABLE `sales_info`(
`sku_id` string COMMENT '商品id', 
`sku_name` string COMMENT '商品名称', 
`category_id3` string COMMENT '三级分类id', 
`price` double COMMENT '销售价格', 
`sales_count` bigint COMMENT '销售数量'
)
COMMENT '商品销售信息表'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
NULL DEFINED AS '' 
STORED AS TEXTFILE
LOCATION
'hdfs://ns1/abc/sales_info'
 
文本内容：
 
[abc]$ cat sales_info.txt 
123,华为Mate10,31,999,20
456,华为Mate30,31,2999,30
789,小米5,31,800,20
1235,小米6,31,900,100
4562,OPPO Findx,31,3900,50
[abc]$ 
 
导入的命令：（从本地导入。此时只写了文件名。如果是从该文件所在的目录直接输入hive切换到hive环境，则写不写绝对路径均可以，如果不是，则需要写文件的绝对路径，比如：'/home/abc/sales_info.txt'。sales_info.txt是否是可执行权限均可以。）
 
hive> load data local inpath 'sales_info.txt' overwrite into table sales_info ;
Loading data to table gdm.sales_info
Table gdm.sales_info stats: [numFiles=1, numRows=0, totalSize=133, rawDataSize=0]
OK
Time taken: 0.347 seconds
 
结果：
 
hive> select * from sales_info ;
OK
123	华为Mate10	31	999.0	20
456	华为Mate30	31	2999.0	30
789	小米5	31	800.0	20
1235	小米6	31	900.0	100
4562	OPPO Findx	31	3900.0	50
Time taken: 0.038 seconds, Fetched: 5 row(s)
hive> 
 
结果：
 
hive> dfs  -ls hdfs://ns1/abc/sales_info;
Found 1 items
-rwxr-xr-x   3 a a       133 2019-04-27 16:33 hdfs://ns1/abc/sales_info/sales_info.txt
hive> 
 
hive> set hive.cli.print.header=true; 
hive> select * from sales_info ;
OK
sku_id	sku_name	category_id3	price	sales_count
123	华为Mate10	31	999.0	20
456	华为Mate30	31	2999.0	30
789	小米5	31	800.0	20
1235	小米6	31	900.0	100
4562	OPPO Findx	31	3900.0	50
Time taken: 0.043 seconds, Fetched: 5 row(s)
hive> 
 
实例2：
 
建表语句（带分区字段，用逗号分隔字段）：
 
CREATE TABLE `sales_info`(
`sku_id` string COMMENT '商品id', 
`sku_name` string COMMENT '商品名称', 
`category_id3` string COMMENT '三级分类id', 
`price` double COMMENT '销售价格', 
`sales_count` bigint COMMENT '销售数量'
)
COMMENT '商品销售信息表'
PARTITIONED BY(
`dt` string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
NULL DEFINED AS '' 
STORED AS TEXTFILE
LOCATION
'hdfs://ns1/abc/sales_info'
 
文本内容（不带分区数据）：
 
[abc]$ cat sales_info.txt 
123,华为Mate10,31,999,20
456,华为Mate30,31,2999,30
789,小米5,31,800,20
1235,小米6,31,900,100
4562,OPPO Findx,31,3900,50
[abc]$ 
 
导入命令：
 
hive> load data local inpath 'sales_info.txt' overwrite into table sales_info ;
FAILED: SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned
hive> load data local inpath 'sales_info.txt' overwrite into table sales_info partition(dt='2019-04-26') ;
Loading data to table gdm.sales_info partition (dt=2019-04-26)
Partition gdm.sales_info{dt=2019-04-26} stats: [numFiles=1, numRows=0, totalSize=133, rawDataSize=0]
OK
Time taken: 0.417 seconds
 
查询结果：
 
hive> select *  from sales_info;
OK
sku_id	sku_name	category_id3	price	sales_count	dt
123	华为Mate10	31	999.0	20	2019-04-26
456	华为Mate30	31	2999.0	30	2019-04-26
789	小米5	31	800.0	20	2019-04-26
1235	小米6	31	900.0	100	2019-04-26
4562	OPPO Findx	31	3900.0	50	2019-04-26
Time taken: 0.049 seconds, Fetched: 5 row(s)
hive> 
 
文本内容（带分区数据）：
 
[abc]$ cat sales_info.txt 
123,华为Mate10,31,999,20,2019-04-26
456,华为Mate30,31,2999,30,2019-04-26
456,华为Mate30,31,2999,30,2019-04-26
789,小米5,31,800,20,2019-04-26
1235,小米6,31,900,100,2019-04-26
4562,OPPO Findx,31,3900,50,2019-04-26
[abc]$ 
 
导入、查询：
 
hive> load data local inpath 'sales_info.txt' overwrite into table sales_info partition(dt='2019-04-26') ;
Loading data to table gdm.sales_info partition (dt=2019-04-26)
Moved: 'hdfs://ns1/abc/sales_info/dt=2019-04-26/sales_info.txt' to trash at: hdfs://ns1/abc/.Trash/Current
Partition gdm.sales_info{dt=2019-04-26} stats: [numFiles=1, numRows=0, totalSize=228, rawDataSize=0]
OK
Time taken: 0.457 seconds
hive> select *  from sales_info;
OK
sku_id	sku_name	category_id3	price	sales_count	dt
123	华为Mate10	31	999.0	20	2019-04-26
456	华为Mate30	31	2999.0	30	2019-04-26
456	华为Mate30	31	2999.0	30	2019-04-26
789	小米5	31	800.0	20	2019-04-26
1235	小米6	31	900.0	100	2019-04-26
4562	OPPO Findx	31	3900.0	50	2019-04-26
Time taken: 0.046 seconds, Fetched: 6 row(s)
hive> 
 
实例3：
 
建表语句（带分区字段,用'\t'分隔字段）：
 
CREATE TABLE `sales_info`(
`sku_id` string COMMENT '商品id', 
`sku_name` string COMMENT '商品名称', 
`category_id3` string COMMENT '三级分类id', 
`price` double COMMENT '销售价格', 
`sales_count` bigint COMMENT '销售数量'
)
COMMENT '商品销售信息表'
PARTITIONED BY(
`dt` string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
NULL DEFINED AS '' 
STORED AS TEXTFILE
LOCATION
'hdfs://ns1/abc/sales_info'
 
数据文本（第一种）：
 
[abc]$ cat sales_info.txt 
123\t华为Mate10\t31\t999\t20
456\t华为Mate30\t31\t2999\t30
789\t小米5\t31\t800\t20
1235\t小米6\t31\t900\t100
4562\tOPPO Findx\t31\t3900\t50
[abc]$ 
 
导入后查询：
 
hive> select  *  from sales_info;
OK
sku_id	sku_name	category_id3	price	sales_count	dt
123\t华为Mate10\t31\t999\t20	NULL	NULL	NULL	NULL	2019-04-26
456\t华为Mate30\t31\t2999\t30	NULL	NULL	NULL	NULL	2019-04-26
789\t小米5\t31\t800\t20	NULL	NULL	NULL	NULL	2019-04-26
1235\t小米6\t31\t900\t100	NULL	NULL	NULL	NULL	2019-04-26
4562\tOPPO Findx\t31\t3900\t50	NULL	NULL	NULL	NULL	2019-04-26
Time taken: 0.89 seconds, Fetched: 5 row(s)
hive> 
 
数据文本（第二种，用Tab键分隔字段）：
 
 
导入后查询：
 
hive> select  *  from sales_info;
OK
sku_id	sku_name	category_id3	price	sales_count	dt
123 华为Mate10  31  999 20	NULL	NULL	NULL	NULL	2019-04-26
456 华为Mate30  31  2999    30	NULL	NULL	NULL	NULL	2019-04-26
456 华为Mate30  31  2999    30	NULL	NULL	NULL	NULL	2019-04-26
789 小米5   31  800 20	NULL	NULL	NULL	NULL	2019-04-26
1235    小米6   1   900 100	NULL	NULL	NULL	NULL	2019-04-26
4562    OPPO Findx  31  900 50	NULL	NULL	NULL	NULL	2019-04-26
Time taken: 0.049 seconds, Fetched: 6 row(s)
hive> 
 
都是错误的，为啥呢？
 
存放数据文件的目录：
 
hive> dfs -ls hdfs://ns1/abc/sales_info/dt=2019-04-26;
Found 1 items
-rwxr-xr-x   3 a a        187 2019-04-27 17:08 hdfs://ns1/abc/sales_info/dt=2019-04-26/sales_info.txt
hive> 
 
注意：
 
1 建表语句中，'STORED AS TEXTFILE' 很重要（select * 才能看到结果），如果是lzo或者orc压缩格式，select * 没结果，但是select count(*) 正确，而且hdfs下的目录也有该文件，
 
比如：
 
CREATE TABLE `sales_info`(
  ...
  )
  COMMENT '商品销售信息表'
PARTITIONED BY ( 
  `dt` string )
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
  NULL DEFINED AS '' 
STORED AS INPUTFORMAT 
  'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://ns1/abc/sales_info'
 
2 导入分区表时，分区字段是否写在数据文件中均可以。
 
 
实例：
 
[...1030temp]$ ll
total 4
-rw-rw-rw- 1 abc abc 192 Nov 14 19:54 test2.log
//直接在linux终端运行报错，需要切换到hive
[abc@BJHTYD-Client-28-138 1030temp]$ load data inpath 'hdfs://...abc/tmp.db/test_1030/test2.log' overwrite into table  tmp.test_1030_external_dt partition (dt='2019-11-11') ;
-bash: syntax error near unexpected token `('

//也报错，需要把表名带单引号
hive> load data local  inpath test2.log overwrite into table  tmp.test_1030_external_dt partition (dt='2019-11-11') ;
MismatchedTokenException(27!=320)
	at org.antlr.runtime.BaseRecognizer.recoverFromMismatchedToken(BaseRecognizer.java:617)
	at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
	at org.apache.hadoop.hive.ql.parse.HiveParser.loadStatement(HiveParser.java:1897)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 1:24 mismatched input 'test2' expecting StringLiteral near 'inpath' in load statement
//则正确
hive> load data local  inpath 'test2.log' overwrite into table  tmp.test_1030_external_dt partition (dt='2019-11-11') ;
Loading data to table tmp.test_1030_external_dt partition (dt=2019-11-11)
Partition tmp.test_1030_external_dt{dt=2019-11-11} stats: [numFiles=1, numRows=0, totalSize=192, rawDataSize=0]
OK
Time taken: 0.931 seconds
hive> exit;
[abc@BJHTYD-Client-28-138 1030temp]$ ll
total 4
-rw-rw-rw- 1 abc abc 192 Nov 14 19:54 test2.log
[abc@BJHTYD-Client-28-138 1030temp]$ 

hive> dfs -du -h hdfs://...abc/tmp.db/test_1030_external_dt; //导入前没有
hive> dfs -du -h hdfs://...abc/tmp.db/test_1030_external_dt; //导入后多个这个文件
192  hdfs://...abc/tmp.db/test_1030_external_dt/dt=2019-11-11

//再add partition 则能正常查询

[... 1030temp]$ ll
total 4
-rw-rw-rw- 1 abc abc 192 Nov 14 19:54 test2.log  //原文件依然存在
 
 
 
 

hive> load data inpath 'hdfs://ns1/abc/sales_info/hello/sales_info.txt' overwrite into table sales_info partition(dt = '2019-04-26');
 
1
 
原数据文件（已经不存在了，是从原路径移动到了新路径下）：
 
如果从本地导入后本地的原数据文件依然存在，相当于复制过去；如果是从hdfs导入，则原数据文件不存在，相当于剪切过去。
 
2
 
如果目的分区之前已存在，会把目的分区的之前的文件移动到回收站；
 
如果目的分区之前不存在，则在移动文件后，会自动把新分区加好，不用再手动添加新分区。
 
建表语句：
 
CREATE TABLE `sales_info`(
`sku_id` string COMMENT '商品id', 
`sku_name` string COMMENT '商品名称', 
`category_id3` string COMMENT '三级分类id', 
`price` double COMMENT '销售价格', 
`sales_count` bigint COMMENT '销售数量'
)
COMMENT '商品销售信息表'
PARTITIONED BY(
`dt` string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
NULL DEFINED AS '' 
STORED AS TEXTFILE
LOCATION
'hdfs://ns1/abc/sales_info'
 
数据内容：
 
[abc]$ cat sales_info.txt 
12377,华为Mate10,31,999,20
45677,华为Mate30,31,2999,30
[abc]$ 
 
在hdfs新建文件夹(hello)，把本地文件put到hdfs目的路径中：
 
hive> dfs -mkdir hdfs://ns1/abc/sales_info/hello;
hive> dfs -put sales_info.txt hdfs://ns1/abc/sales_info/hello;
hive> dfs -ls hdfs://ns1/user/mart_tha/gdm.db/sales_info/hello;
Found 1 items
-rw-r--r--   3 a a 61 2019-04-27 17:34 
 
（put后，原文件依然存在，相当于复制）
 
导入数据（新建表后，之前导入过一次，这是第二次导入）、查询结果（有2条数据，是最新的，之前是5条数据）：
 
hive> load data inpath 'hdfs://ns1/abc/sales_info/hello/sales_info.txt' overwrite into table sales_info partition(dt = '2019-04-26');
Loading data to table gdm.sales_info partition (dt=2019-04-26)
Moved: 'hdfs://ns1/abc/sales_info/dt=2019-04-26/sales_info.txt' to trash at: hdfs://ns1/abc/.Trash/Current
Partition gdm.sales_info{dt=2019-04-26} stats: [numFiles=1, numRows=0, totalSize=61, rawDataSize=0]
OK
Time taken: 0.43 seconds
hive> select *  from sales_info;
OK
sku_id	sku_name	category_id3	price	sales_count	dt
12377	华为Mate10	31	999.0	20	2019-04-26
45677	华为Mate30	31	2999.0	30	2019-04-26
Time taken: 0.049 seconds, Fetched: 2 row(s)
 
再查看原数据文件（已经不存在了，是从原路径移动到了新路径下）：
 
hive> dfs -ls hdfs://ns1/user/mart_tha/gdm.db/sales_info/hello;
hive> 
 
实例2：
 
hive> dfs -du -h hdfs://.../tmp.db/test_1030_external_dt; //无分区和文件
hive> dfs -du -h hdfs://.../tmp.db/test_1030;
19.5 K  hdfs://.../tmp.db/test_1030/000000_0
192     hdfs://.../tmp.db/test_1030/test2.log //load这个文件
hive> load data inpath 'hdfs://.../tmp.db/test_1030/test2.log' overwrite into table  tmp.test_1030_external_dt partition (dt='2019-11-11') ;
Loading data to table tmp.test_1030_external_dt partition (dt=2019-11-11)
Partition tmp.test_1030_external_dt{dt=2019-11-11} stats: [numFiles=1, numRows=0, totalSize=192, rawDataSize=0]
OK
Time taken: 0.777 seconds
hive> dfs -du -h hdfs://.../tmp.db/test_1030;
19.5 K  hdfs://.../tmp.db/test_1030/000000_0 //有分区和文件
hive> dfs -du -h hdfs://.../tmp.db/test_1030_external_dt;
192  hdfs://.../tmp.db/test_1030_external_dt/dt=2019-11-11 //原文件已经不存在了,说明是移动
hive> dfs -ls  hdfs://.../tmp.db/test_1030_external_dt/dt=2019-11-11;
Found 1 items
-rwxr-xr-x   3 jdw_traffic jdw_traffic        192 2019-11-14 19:12 hdfs://.../tmp.db/test_1030_external_dt/dt=2019-11-11/test2.log //文件的时间没有变化
 
 
 
 

此导入方法适用于元数据库与目标数据库要导入的表数据结构一致
1.登录元数据库
2.选定元数据库的用户
3.右击用户点击task--》export data



4.点击下一步，按下方的红色填写，第一个表示数据库，第二个填元数据源的用户名
5.下一步会出现上图的框，根据描述填写对应目标数据表的信息
6.接下来及步骤一直点击下一步，直到下图；左面的是元数据库信息，右面的是目标数据库信息。选定到对应的表
7.点击下一步，然后就完成数据的导入