MySQL 插入复制某一列的数据遇到的问题和解决方法
因为数据量比较多,
Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
1.我的初衷是把同一个表里的某一行的数据进行复制,制作多条重复用来测试使用,比如测试脚本升级后重复数据被处理消失,准备数条重复数据
insert into [MAP_ADDRESSPOINT] select * from [MAP_ADDRESSPOINT]
但是报错,因为复制的话导致主键被重复是不被允许的
workaround:
insert into [MAP_ADDRESSPOINT](ObJECTID,[Street],[XCoord],[YCoord],[Lock],[City],[State],[RelateId],[IsCentroid],[PostalCode],[Shape],[AddressNumber])
select
( select (MAX( OBJECTID)+1) from [MAP_ADDRESSPOINT] as ObJECTID),
[Street],[XCoord],[YCoord],[Lock],[City],[State],[RelateId],[IsCentroid],[PostalCode],[Shape],[AddressNumber]
from [MjAP_ADDRESSPOINT] where OBJECTID = 341如此
FInished!