跨服务器查询sql语句样例
INSERT INTO [目标数据库名].[dbo].[表名]
([Target_ID]
,[Target_Name])
lect * from
OPENDATASOURCE('SQLOLEDB','Data Source=服务器;DBN=源数据库名;UID=用户名;PWD=密码').源数据库名.dbo.[表名]
lect * from
OPENDATASOURCE('SQLOLEDB','Data Source=DATHKKWA009;DBN=AX50SP1_PROD;UID=webax2009;PWD=webax2009~!@#').AX50SP1_PROD.dbo.[BOMWHEREUSE]
店铺广告lect * from
OPENDATASOURCE('SQLNCLI','data source=DATHKKWA009;initial catalog=AX50SP1_PROD;ur id=webax2009;password=webax2009~!@#').AX50SP1_PROD.dbo.[BOMWHEREUSE]
lect t.prodLine,s.nSort, r.FIRSTREQDATE, r.PRODID,p.TOOLINGNUM,p.ITEMID,p.NAME,p.lingChangeType
from OPENDATASOURCE('SQLNCLI', 'data source=10.105.1.199;initial catalog=RocomDEV;ur id=webax2009;password=webax2009~!@#').RocomDEV.dbo.ProdReqTransSettledTable r
left join OPENDATASOURCE('SQLNCLI', 'data source=10.105.1.199;initial catalog=RocomDEV;ur id=webax2009;password=webax2009~!@#').RocomDEV.dbo.prodTable p on r.prodid=p.prodid
left Sort s on s.prodID=r.prodID
left lingLine t on t.prodLinePriority=1 lingNum=p.TOOLINGNUM
where p.REMAININVENTPHYSICAL>0
order by 1,2,3
SELECT *
into #tmp
FROM OPENROWSET('SQLNCLI', 'rver=10.105.22.184;uid=sa;pwd=***********',
't fmtonly off ; EXEC ROCOMHR.dbo.mfsp_connect_eip_att ''2018-12-23'',''2019-01-23'','''',''2''') AS a
SELECT *
FROM OPENROWSET('SQLNCLI', '10.105.22.184';'sa';'**********',
't fmtonly off ; EXEC ROCOMHR.dbo.mfsp_connect_eip_att ''2018-01-21'',''2018-01-21'','''',''2''') AS a
SELECT *
into #tmp
FROM OPENROWSET('SQLNCLI', '10.105.22.184';'sa';'**********',
't fmtonly off ; EXEC ROCOMHR.dbo.mfsp_connect_eip_att ''2018-01-21'',''2018-01-21'','''',''2''') AS a
lect * from #tmp
drop table #tmp
inrt into dbo.FactOvertime(overtimeDate,EmployeeID,TotalTime)
年度考核自我评鉴lect rq,zgbh,jb
from #tmp t河虾的家常做法
where not exists (lect employeeid from dbo.FactOvertime where overtimeDate=t.rq and bh collate databa_default)
drop table #tmp
SELECT c.*, o.*
幼儿作品FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
但上述查询需要开启外围数据库查询
先选中 master 数据库，新建一个查询，执行以下的sql
开启外围： 需要权限
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
学生祝福语
reconfigure
-
------------------------------
小米盒子投屏关闭外围：需要权限
入党申请书1500字exec sp_configure 'show advanced options',0
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
创建链接服务器
exec sp_addlinkedrver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地
址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'fal ',null, '用户名 ', '密码 '
--查询示例
lect * from ITSV.数据库名.dbo.表名
--导入示例
lect * into 表 from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_droprver 'ITSV ', 'droplogins '
--连接远程/局域网数据(openrowt/openquery/opendatasource)
--1、openrowt
--查询示例
lect * from openrowt( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 '，数据库名.dbo.表名)
--生成本地表
lect * into 表 from openrowt( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 '，数据库名.dbo.表名)
-
小船怎么画-把本地表导入远程表
inrt openrowt( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 '，数据库名.dbo.表名)
lect *from 本地表
--更新本地表
update b
t b.列A=a.列A
from openrowt( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 '，数据库名.dbo.表名)as a inner join 本地表 b
lumn1
