但是两个数据库中的两张表有重复的记录的,在不删除重复记录的情况下把差异的记录插入到USER022007数据库的这张表里面。
我在查询分析器里面执行了以下语句,但提示FROM附近有语法错误。
select * insert into user022007.z_bmry
from user012007.z_bmry
where user012007.z_bmry.dm<>user022007.z_bmry.dm
6 个解决方案
#1
insert into user022007.z_bmry
select * from user012007.z_bmry where dm not in (select dm from user022007.z_bmry)
select * from user012007.z_bmry where dm not in (select dm from user022007.z_bmry)
#2
use USER022007 --进入你要操作的数据库
go
insert into z_bmry
select * from USER012007.dbo.z_bmry where --将全部记录复制
go
----------仿照做吧
use mySQL
go
create table t(name varchar(10))
insert t select 'a'
union all select 'b'
union all select 'c'
go
use CnForums
go
create table t(Ename varchar(10))
insert t select 'a'
union all select 'e'
union all select 'g'
go
select * from CnForums.dbo.t
go
select * from mySQL.dbo.t
go
insert into CnForums.dbo.t select name from mySQL.dbo.t where name not in (select Ename from CnForums.dbo.t) --插入不重复的记录
#3
注意 CnForums.dbo.t 中的dbo 是一定要有不然会不识别的
#4
INSERT INTO USER022007.Z_bmry
SELECT * FROM USER012007.Z_bmry
WHERE not exists(SELECT * FROM user022007.z_bmry WHERE dm=user012007.z_bmry.dm)
SELECT * FROM USER012007.Z_bmry
WHERE not exists(SELECT * FROM user022007.z_bmry WHERE dm=user012007.z_bmry.dm)
#5
用上面的语句提示我
user012007无效
user022007无效
user012007无效
user022007无效
#6
提示无效
#1
insert into user022007.z_bmry
select * from user012007.z_bmry where dm not in (select dm from user022007.z_bmry)
select * from user012007.z_bmry where dm not in (select dm from user022007.z_bmry)
#2
use USER022007 --进入你要操作的数据库
go
insert into z_bmry
select * from USER012007.dbo.z_bmry where --将全部记录复制
go
----------仿照做吧
use mySQL
go
create table t(name varchar(10))
insert t select 'a'
union all select 'b'
union all select 'c'
go
use CnForums
go
create table t(Ename varchar(10))
insert t select 'a'
union all select 'e'
union all select 'g'
go
select * from CnForums.dbo.t
go
select * from mySQL.dbo.t
go
insert into CnForums.dbo.t select name from mySQL.dbo.t where name not in (select Ename from CnForums.dbo.t) --插入不重复的记录
#3
注意 CnForums.dbo.t 中的dbo 是一定要有不然会不识别的
#4
INSERT INTO USER022007.Z_bmry
SELECT * FROM USER012007.Z_bmry
WHERE not exists(SELECT * FROM user022007.z_bmry WHERE dm=user012007.z_bmry.dm)
SELECT * FROM USER012007.Z_bmry
WHERE not exists(SELECT * FROM user022007.z_bmry WHERE dm=user012007.z_bmry.dm)
#5
用上面的语句提示我
user012007无效
user022007无效
user012007无效
user022007无效
#6
提示无效