当前位置：首页 > news >正文

SQL跨数据库复制表数据

news 来源：原创 2024/5/21 0:47:37

Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。

1.INSERT INTO SELECT语句

语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：

--1.创建测试表
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]

    create TABLE Table2
    (
        a varchar(10),
        c varchar(10),
        d int,
        CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO
    --2.创建测试数据
    Insert into Table1 values('赵','asds','90')
    Insert into Table1 values('钱','asds','100')
    Insert into Table1 values('孙','asds','80')
    Insert into Table1 values('李','asds',null)
    GO
    select * from Table2

    --3.INSERT INTO SELECT语句复制表数据
    Insert into Table2(a, c, d) select a,c,5 from Table1
    GO

    --4.显示更新后的结果
    select * from Table2
    GO
    --5.删除测试表
    drop TABLE Table1
    drop TABLE Table2

INSERT INTO SELECT语句复制表数据

2.SELECT INTO FROM语句

语句形式为：SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：

 --1.创建测试表
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO

    --2.创建测试数据
    Insert into Table1 values('赵','asds','90')
    Insert into Table1 values('钱','asds','100')
    Insert into Table1 values('孙','asds','80')
    Insert into Table1 values('李','asds',null)
    GO

    --3.SELECT INTO FROM语句创建表Table2并复制数据
    select a,c INTO Table2 from Table1
    GO

    --4.显示更新后的结果
    select * from Table2
    GO
    --5.删除测试表
    drop TABLE Table1
    drop TABLE Table2

SELECT INTO FROM创建表并复制表数据

------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------

/**
1.开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure


2.关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
*/

--网络查询
select * from openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_info) where moduleAlias = 'batch_DataCJRWSB' order by sort
select * from openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_infoItem) where moduleAlias = 'batch_DataCJRWSB' order by sort

--本地查询
select * from batch_Info where moduleAlias = 'batch_DataCJRWSB'
order by sort
select * from batch_InfoItem where moduleAlias = 'batch_DataCJRWSB'
order by sort


--本地数据库导入到 网络数据库
insert into openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_info) 
select * from batch_Info where moduleAlias = 'batch_DataCJRWSB' order by sort

insert into openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_InfoItem) 
select * from batch_InfoItem where moduleAlias = 'batch_DataCJRWSB' order by sort

  1 不同服务器数据库之间的数据操作
  2 不同数据库之间复制表的数据的方法：
  3 当表目标表存在时：
  4 insert into 目的数据库..表 select * from 源数据库..表  
  5 当目标表不存在时：
  6 select * into 目的数据库..表 from 源数据库..表
  7 --如果在不同的SQL之间: 
  8 
  9 insert into openrowset('sqloledb','目的服务器名';'sa';'',目的数据库.dbo.表) 
 10 select * from 源数据库..表 
 11 
 12 --创建链接服务器 
 13 exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
 14 exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' 
 15 
 16 --查询示例 
 17 select * from ITSV.数据库名.dbo.表名 
 18 
 19 --导入示例 
 20 select * into 表 from ITSV.数据库名.dbo.表名 
 21 
 22 --以后不再使用时删除链接服务器 
 23 exec sp_dropserver 'ITSV ', 'droplogins ' 
 24 
 25 --连接远程/局域网数据(openrowset/openquery/opendatasource) 
 26 --1、openrowset 
 27 
 28 --查询示例 
 29 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
 30 
 31 --生成本地表 
 32 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
 33 
 34 --把本地表导入远程表 
 35 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
 36 select *from 本地表 
 37 
 38 --更新本地表 
 39 update b 
 40 set b.列A=a.列A 
 41 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b 
 42 on a.column1=b.column1 
 43 
 44 --openquery用法需要创建一个连接 
 45 
 46 --首先创建一个连接创建链接服务器 
 47 exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
 48 --查询 
 49 select * 
 50 FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') 
 51 --把本地表导入远程表 
 52 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') 
 53 select * from 本地表 
 54 --更新本地表 
 55 update b 
 56 set b.列B=a.列B 
 57 FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a 
 58 inner join 本地表 b on a.列A=b.列A 
 59 
 60 --3、opendatasource/openrowset 
 61 SELECT   * 
 62 FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta 
 63 --把本地表导入远程表 
 64 insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 
 65 select * from
 66 -----------------------------------------------------------------------------------------------------------------------------------
 67 好吧，如果上面看得烦下面有个更容易理解的例子：
 68 Exec sp_droplinkedsrvlogin DBVIP,Null
 69 Exec sp_dropserver DBVIP
 70 EXEC sp_addlinkedserver
 71       @server='DBVIP',--被访问的服务器别名 
 72       @srvproduct='',
 73       @provider='SQLOLEDB',
 74       @datasrc='Server2'   --要访问的服务器
 75 
 76 EXEC sp_addlinkedsrvlogin 
 77      'DBVIP', --被访问的服务器别名
 78      'false', 
 79      NULL, 
 80      'sa', --帐号
 81      'thankyoubobby' --密码
 82 
 83 Select   *   from DBVIP.pubs.dbo.orders   
 84 
 85 
 86 /ORACLE
 87 Exec sp_droplinkedsrvlogin demo,Null
 88 Exec sp_dropserver demo
 89 go
 90 EXEC sp_addlinkedserver 
 91         @server ='demo',
 92         @srvproduct='Oracle',
 93    @provider='MSDAORA', 
 94    @datasrc='ServiceName' 
 95   
 96 EXEC sp_addlinkedsrvlogin 
 97      'demo', 
 98      'false', 
 99      NULL, 
100      'userid', 
101      'password' 
102 go
103 SELECT * FROM OPENQUERY(demo ,'select * from tbdemo' )
104 UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101') 
105 SET name = 'hello';
106 INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')
107 VALUES ('hello');
108 DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name = ''hello''');
109 -------------------------------------------具体例子------------------------------------------------------------
110 if   exists(select   1   from   master.dbo.sysservers   where   srvname   =   'test')   
111 begin   
112 exec   sp_droplinkedsrvlogin     'test','sa'   
113 exec   sp_dropserver     'test'   
114 end  
115 --建立连接服务器 
116 EXEC sp_addlinkedserver 'test', 'ms','SQLOLEDB', '192.168.1.99'
117 
118 exec sp_addlinkedsrvlogin 'test','false',null,'sa',''
119 select * from test.db_film.dbo.T_film
120 go
121 if   exists(select   1   from   master.dbo.sysservers   where   srvname   =   'test')   
122 begin   
123 exec   sp_droplinkedsrvlogin     'test','sa'   
124 exec   sp_dropserver     'test'   
125 end  
126 go