Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "how to query across databases in SQL Server". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Basic statement

SELECT * FROM database A.dbo. Table An a, database B.dbo. Table B b WHERE a.field=b.field

"DBO" can be omitted, such as

SELECT * FROM database A.. Table An a, database B.. Table B b WHERE a.field=b.field

SqlServer database:

-- this is to map a remote database EXEC sp_addlinkedserver'IP or hostname of the remote database', N'SQL Server'-- this sentence is to log in to the remote database EXEC sp_addlinkedsrvlogin'IP or hostname', 'false', NULL,' 'login name', 'password'-- after logging in, you can manipulate the object in the remote database select * from [the IP or hostname of the remote database] in the following format. [database name]. [dbo]. [table name] insert into openrowset ('sqloledb','192.168.0.100';'sa';'10060','select * from knss2009.dbo.yw_kck') select * from yw_kck + +

Data manipulation between different server databases

Create a linked server

Exec sp_addlinkedserver 'ITSV','', 'SQLOLEDB', 'remote server name or ip address' exec sp_addlinkedsrvlogin 'ITSV', 'false', null, 'username', 'password'

Query example

Select * from ITSV. Database name. Dbo. Table name

Import exampl

Select * into table from ITSV. Database name. Dbo. Table name

Delete linked servers when they are no longer used in the future

This is the end of exec sp_dropserver 'ITSV', 'droplogins' and "how to query across databases". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.