insert into DB1.table select distinct * from DB2.table
insert into DB2.dbo.table ( select * from DB1.dbo.table except select * from DB2.dbo.table)
-- 同一个服务器下 insert into DB1.table select distinct * from DB2.table--不同服务器下,需要提前建一下连接服务器insert into [192.168.1.1].DB1.table select distinct * from [192.168.1.2].DB2.table
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1 <>1 法二:select top 0 * into b from a 2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) insert into b(a, b, c) select d,e,f from a; 3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件 例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
-- 同一个服务器下 insert into DB1.table select distinct * from DB2.table--不同服务器下,需要提前建一下连接服务器insert into [192.168.1.1].DB1.table select distinct * from [192.168.1.2].DB2.table
--SQL2005 insert into DB2.dbo.[table] select * from ( select * from DB1.dbo.[table] except select * from DB2.dbo.[table] ) as t--SQL2000:如果表有主键IDinsert into DB2.dbo.[table] select * from DB1.dbo.[table] t where not exists( select 1 from DB2.dbo.[table] where id=t.id)
insert into DB2.dbo.table select ( select * from DB1.dbo.table except select * from DB2.dbo.table) * 修改下我上面写的
--如果没有自增字段的话,是这样。 insert db2..tb select * from db1..tb a where not exists(select 1 from db2..tb where 不重复字段1=a.不重复字段1 and 不重复字段2=a.不重复字段2)--依次类推 --如果有自增字段的话,在字段列表里不加这个字段
insert into DB1.table select distinct * from DB2.table
应该用INTERSECT 。1、如果DB1,DB2在同一服务器的实例上。 insert into DB2.dbo.table ( select * from DB1.dbo.table INTERSECT select * from DB2.dbo.table)2、如果DB1,DB2在不同服务器的实例上。 先建立链接服务器,参考。 insert into DB2.dbo.table ( select * from [远程服务器IP].DB1.dbo.table INTERSECT select * from DB2.dbo.table)
源表:a 新表:b 1、复制表(只复制表结构,不复制数据) -------------------------------------------------------------------------------------------------------------------- A1: select * into b from a where 1<>1 A2: select top 0 * into b from a ---------------------------------------------------------------------------------------------------------------------2、拷贝表(拷贝数据) --------------------------------------------------------------------------------------------------------------------- A: insert into b(A,B,C) select D,E,F from a where ...... ---------------------------------------------------------------------------------------------------------------------3、跨数据库之间表的复制(源表a在数据库db_a中,新表b在数据库db_b中) --------------------------------------------------------------------------------------------------------------------- A1:只复制表结构(在数据库db_b中执行) select top 0 * into b from db_a.dbo.a 或者:select * into b from db_a.dbo.a where 1<>1 A2:数据和表一起拷贝(在数据库db_b中执行) insert into b(A,B,C) select D,E,F from db_a.dbo.a where ......
(
select * from DB1.dbo.table
except
select * from DB2.dbo.table)
-- 同一个服务器下
insert into DB1.table select distinct * from DB2.table--不同服务器下,需要提前建一下连接服务器insert into [192.168.1.1].DB1.table select distinct * from [192.168.1.2].DB2.table
法一:select * into b from a where 1 <>1
法二:select top 0 * into b from a 2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b(a, b, c) select d,e,f from a; 3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
-- 同一个服务器下
insert into DB1.table select distinct * from DB2.table--不同服务器下,需要提前建一下连接服务器insert into [192.168.1.1].DB1.table select distinct * from [192.168.1.2].DB2.table
insert into DB2.dbo.[table]
select *
from (
select * from DB1.dbo.[table]
except
select * from DB2.dbo.[table]
) as t--SQL2000:如果表有主键IDinsert into DB2.dbo.[table]
select * from DB1.dbo.[table] t
where not exists(
select 1
from DB2.dbo.[table]
where id=t.id)
select
(
select * from DB1.dbo.table
except
select * from DB2.dbo.table) *
修改下我上面写的
insert db2..tb select * from db1..tb a where
not exists(select 1 from db2..tb where
不重复字段1=a.不重复字段1 and 不重复字段2=a.不重复字段2)--依次类推
--如果有自增字段的话,在字段列表里不加这个字段
insert into DB2.dbo.table
(
select * from DB1.dbo.table
INTERSECT
select * from DB2.dbo.table)2、如果DB1,DB2在不同服务器的实例上。
先建立链接服务器,参考。
insert into DB2.dbo.table
(
select * from [远程服务器IP].DB1.dbo.table
INTERSECT
select * from DB2.dbo.table)
源表:a 新表:b
1、复制表(只复制表结构,不复制数据)
--------------------------------------------------------------------------------------------------------------------
A1:
select * into b from a where 1<>1
A2:
select top 0 * into b from a
---------------------------------------------------------------------------------------------------------------------2、拷贝表(拷贝数据)
---------------------------------------------------------------------------------------------------------------------
A:
insert into b(A,B,C)
select D,E,F from a
where ......
---------------------------------------------------------------------------------------------------------------------3、跨数据库之间表的复制(源表a在数据库db_a中,新表b在数据库db_b中)
---------------------------------------------------------------------------------------------------------------------
A1:只复制表结构(在数据库db_b中执行)
select top 0 * into b from db_a.dbo.a
或者:select * into b from db_a.dbo.a where 1<>1
A2:数据和表一起拷贝(在数据库db_b中执行)
insert into b(A,B,C)
select D,E,F from db_a.dbo.a
where ......