[MSSQL] OPENROWSET ,OPENDATASOURCE,OPENQUERY / Linked Server(연결된서버) 개념 및 장단점, 사용방법

같은 네트워크(물리적인 서버)가 동일하다면 걱정할 필요없이 단순히 [서버].[dbo].[테이블명]을 통해 조회가 가능하지만,

 

다른 DB정보를 가져올때는 다른 여러 방법을 통하여 정보를 가져올 수 있다.

 

[연결된 서버(LINKED SERVER)]

* 다른 외부 서버와의 연결

   자주 사용되는 경우 성능적인 측면이 요구되므로 해당 방법이 최우선 고려 대상

   /* MSSQL 연결된 서버 생성 */
   EXEC sp_addlinkedserver
      @server = '[연결된 서버별칭]',
      @srvproduct = '',
      @provider = 'SQLOLEDB',
      @datasrc = '[서버 아이피]',
      @catalog = '[데이터 베이스명]'
      
   /* MSSQL 연결계정 생성 */
   EXEC sp_addlinkedsrvlogin
      @rmtsrvname= '[연결된 서버별칭]',
      @useself= 'false',
      @rmtuser = '[사용자 이름]',
      @rmtpassword = '[사용자 암호]'
   
   /* MSSQL 연결된 서버 확인*/
   SELECT * FROM master.dbo.sysservers WHERE srvname = '[연결된 서버별칭]'
   
   /* MSSQL 연결계정 확인 */
   SELECT * FROM master.sys.linked_logins WHERE remote_name = '[사용자 이름]'   
   
   /*연결된 서버 사용*/
   SELECT [컬럼명] FROM [연결된 서버별칭].[데이터 베이스명].[데이터베이스 소유자명].[테이블명] WHERE [조건절]   
   
   /*연결된 서버 삭제[연결된 계정이 있을경우 계정먼저 삭제]*/
   EXEC sp_dropserver
      @server = '[연결된 서버별칭]'   
      
   /*연결된 계정 삭제*/
   EXEC sp_droplinkedsrvlogin
      @rmtsrvname= '[연결된 서버별칭]',
      @locallogin = NULL

   

 

[OPENROWSET & OPENDATASOURCE ]

* LINKED SERVER를 이용하지 않고, 커넥션스트링을 이용하여 바로 연결한다.

  자주 사용되지 않는 연결시에는 OPENROWSET을 활용하게되며, LINKED SERVER에 비해 성능은 부족하다.

* txt,json 등 물리파일을 읽어서 대용량(BULK) INSERT 가능

* OPENDATASOURCE는 변수를 인수로 받아들이지 않음

 

/*OPENROWSET 활성화를 위한 아래 구문 실행
EXEC SP_CONFIGURE'show advanced options', 1
EXEC SP_CONFIGURE'Ad Hoc Distributed Queries', 1
*/

--파일 전체 내용 읽기(BULK)
INSERT INTO myTable(content)   
   SELECT BulkColumn
          FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
          
 --json 파일 읽기 
 --openrowset(bulk)를 이용하여 json파일 전체 내용을 읽기 쉽도록 표시, 
 --json구문을 분석하고 열을 반환하는 oepnjson 함수에 BulkColumn을 제공 
   SELECT book.*
     FROM OPENROWSET (BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) as j
          CROSS APPLY OPENJSON(BulkColumn)
          WITH( id nvarchar(100), name nvarchar(100), price float,
                pages int, author nvarchar(100)) AS book
                
--원격 연결 정보 가져오기
SELECT * FROM 
OPENROWSET('SQLOLEDB','IP정보';'유저ID';'PWD', 'SELECT * FROM 원격테이블')

 

[OPENQUERY]

MSSQL간의 연결이 아닌 다른기종의 데이터베이스와 데이터 정보 조회 시 이용한다.

(선행조건: 연결된서버(LINKED SERVER))

/* 타기종 DB와의 정보조회 */
SELECT [컬럼명] FROM OPENQUERY ([연결된 서버별칭], 'SELECT [컬럼명] FROM [테이블명]')

 

 

 

 

* 참조 

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/openrowset-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

OPENROWSET(Transact-SQL) - SQL Server

https://sodocumentation.net/ko/sql-server/topic/7330/%EB%8C%80%EB%9F%89-%EA%B0%80%EC%A0%B8-%EC%98%A4%EA%B8%B0

Microsoft SQL Server - 대량 가져 오기 | sql-server Tutorial

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/opendatasource-transact-sql?view=sql-server-ver15

OPENDATASOURCE(Transact-SQL) - SQL Server